mysql pivot table result of json fields

Question

mysql pivot table result of json fields

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm looking for information to pivot a table in mysql. The bbdd that I have is a bit strange, because the data I have to get from a json of one of the fields. After searching, I managed to execute the following sentence:

    select
            count(if( json_extract(filters, '$.assembled')= true,json_extract(filters, '$.assembled'),NULL)) as assembled,
            count(if( json_extract(filters, '$.osWindows')= true,json_extract(filters, '$.osWindows'),NULL)) as windows,
            count(if( json_extract(filters, '$.osMac')= true,json_extract(filters, '$.osMac'),NULL)) as mac,
            count(if( json_extract(filters, '$.osLinux')= true,json_extract(filters, '$.osLinux'),NULL)) as linux,
            count(if( json_extract(filters, '$.sdCard')= true,json_extract(filters, '$.sdCard'),NULL)) as sdcard,
            count(if( json_extract(filters, '$.usb')= true,json_extract(filters, '$.usb'),NULL)) as usb,
            count(if( json_extract(filters, '$.ethernet')= true,json_extract(filters, '$.ethernet'),NULL)) as ethernet,
            count(if( json_extract(filters, '$.wifi')= true,json_extract(filters, '$.wifi'),NULL)) as wifi,
            count(if( json_extract(filters, '$.bluetooth')= true,json_extract(filters, '$.bluetooth'),NULL)) as bluetooth,
            count(if( json_extract(filters, '$.integratedDisplay')= true,json_extract(filters, '$.integratedDisplay'),NULL)) as integratedDisplay,
            count(if( json_extract(filters, '$.touchableDisplay')= true,json_extract(filters, '$.touchableDisplay'),NULL)) as touchableDisplay, 
from bigdata_filtered

And I have obtained the following result:

+-----------+---------+-----+-------+--------+-----+----------+------+-----------+-------------------+------------------+
| assembled | windows | mac | linux | sdcard | usb | ethernet | wifi | bluetooth | integratedDisplay | touchableDisplay |
+-----------+---------+-----+-------+--------+-----+----------+------+-----------+-------------------+------------------+
|         2 |       0 |   0 |     0 |      0 |   0 |       14 |   13 |         0 |                 0 |                0 |
+-----------+---------+-----+-------+--------+-----+----------+------+-----------+-------------------+------------------+

Now I need to get something like:

property         count
assembled           0
windows             0
mac                 0
linux               0
sdcard              0
usb                 0
ethernet           14
wifi               13
bluetooth           0
integratedDisplay   0
touchableDisplay    0

I've seen tutorials on how to pivot with mysql, but all the examples are to get the opposite (From what I want to do, show how to get what I already have)

Does anyone think of how I can do it? or know any tutorial that explains this particular case?

mysql json

asked by Jakala 05.12.2016 в 09:50

source

2 answers

I can not get the facebook token Help with Oracle SQL query 11g

score 0 · Answer 1

Seeing what you comment, it occurs to me that, taking advantage of their being finite values, make a query for each type that returns two values, a constant (which will be the name) and the value for that name and then join all with a union (I paint it to you on the flight, which is probably wrong, but so you can take my idea to see if it works):

select 
    'assembled' as property,
    count(json_extract(filters, '$.assembled')) as records
            from bigdata_filtered
where json_extract(filters, '$.assembled')= true

union all

select 
    'osWindows' as property,
    count(json_extract(filters, '$.osWindows')) as records
            from bigdata_filtered
where json_extract(filters, '$.osWindows')= true

union all

...
...
...

I think it's not quite right but I think you get the idea.

I hope it serves you.

score 0 · Answer 2

Based on Fernando's comment, in the end I left the query as follows:

  select concat("assembled") as field, count(if( json_extract(filters, '$.assembled')= true,json_extract(filters, '$.assembled'),NULL)) / count(id) * 100 as count
  from bigdata_filtered 
union 
  select concat("windows") as field, count(if( json_extract(filters, '$.osWindows')= true,json_extract(filters, '$.osWindows'),NULL))/ count(id) * 100 as count
  from bigdata_filtered
union
  select concat("mac") as field,  count(if( json_extract(filters, '$.osMac')= true,json_extract(filters, '$.osMac'),NULL))/ count(id) * 100 as count
  from bigdata_filtered
union
  select concat("linux") as field,  count(if( json_extract(filters, '$.osLinux')= true,json_extract(filters, '$.osLinux'),NULL))/ count(id) * 100 as count
  from bigdata_filtered
union
  select concat("sdCard") as field,  count(if( json_extract(filters, '$.sdCard')= true,json_extract(filters, '$.sdCard'),NULL))/ count(id) * 100 as count
  from bigdata_filtered
union
  select concat("usb") as field,  count(if( json_extract(filters, '$.usb')= true,json_extract(filters, '$.usb'),NULL))/ count(id) * 100 as count
  from bigdata_filtered
union
  select concat("ethernet") as field,  count(if( json_extract(filters, '$.ethernet')= true,json_extract(filters, '$.ethernet'),NULL))/ count(id) * 100 as count
  from bigdata_filtered
union
  select concat("wifi") as field,  count(if( json_extract(filters, '$.wifi')= true,json_extract(filters, '$.wifi'),NULL))/ count(id) * 100 as count
  from bigdata_filtered
union
  select concat("bluetooth") as field,  count(if( json_extract(filters, '$.bluetooth')= true,json_extract(filters, '$.bluetooth'),NULL))/ count(id) * 100 as count
  from bigdata_filtered
union
  select concat("integratedDisplay") as field,  count(if( json_extract(filters, '$.integratedDisplay')= true,json_extract(filters, '$.integratedDisplay'),NULL))/ count(id) * 100 as count
  from bigdata_filtered
union
  select concat("touchableDisplay") as field,  count(if( json_extract(filters, '$.touchableDisplay')= true,json_extract(filters, '$.touchableDisplay'),NULL))/ count(id) * 100 as count
  from bigdata_filtered;

I leave it in case someone serves as a solution.

Thank you very much !!!