Folks,
I have a need to create a weekly statistical report that includes data from the extra_fields table. I need to join the items table, field extra_fields to the extra_fields table, field value so that I can extract the dropdown selection. I tried using the JSON format extra_fields->'$.id' but it keeps returning a null value. How can I use MySQL to manipulate these table field formats?
Here's the MySQL that lets me get a result:
SELECT
a.title 'Title',
DATE_FORMAT(a.created, '%m/%e/%Y') 'Date Shared',
a.hits 'Total Hits',
a.extra_fields,
a.extra_fields->"$**.id" AS id,
a.extra_fields->"$**.value" AS value,
JSON_EXTRACT(extra_fields, "$**.id") AS id2
FROM
m7alm_k2_items a
WHERE
a.id = 12942;
The data returned is
id = ["52"]
value = ["51"]
id2 = ["52"]
How can I get rid of the double quotes and brackets? I did try ->> and JSON_UNQUOTE but neither worked for me. I'm running MySQL 5.7.34. Is there a better way to read these data fields?
Thanks in advance for the help!
Joe
I have a need to create a weekly statistical report that includes data from the extra_fields table. I need to join the items table, field extra_fields to the extra_fields table, field value so that I can extract the dropdown selection. I tried using the JSON format extra_fields->'$.id' but it keeps returning a null value. How can I use MySQL to manipulate these table field formats?
Here's the MySQL that lets me get a result:
SELECT
a.title 'Title',
DATE_FORMAT(a.created, '%m/%e/%Y') 'Date Shared',
a.hits 'Total Hits',
a.extra_fields,
a.extra_fields->"$**.id" AS id,
a.extra_fields->"$**.value" AS value,
JSON_EXTRACT(extra_fields, "$**.id") AS id2
FROM
m7alm_k2_items a
WHERE
a.id = 12942;
The data returned is
id = ["52"]
value = ["51"]
id2 = ["52"]
How can I get rid of the double quotes and brackets? I did try ->> and JSON_UNQUOTE but neither worked for me. I'm running MySQL 5.7.34. Is there a better way to read these data fields?
Thanks in advance for the help!
Joe
Edited 5 years 5 months ago by Joseph Rebele