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

Fortis,

Thanks in advance for your help! What I am attempting to do is to build a daily export in CSV from my K2 Items table that contains some Extra Fields. What I utimately want to do is end with the following CSV formatted file:

Source, Title, Date Shared, Hits

The Source is an Extra Field that contains all US States and other Resource Submitters. Title is from the K2 Items table, Date Shared is the K2 item created date, and hits is from the K2 items table.The SQL I attached is my attempt at writing the SQL to pull the Source field. Additionally, I attached a CSV of the data that is returned.

I really appreciate the help!

Regards,
Joe Rebele

Fortis,

Have you had the opportunity to look at this query?

Thanks,
Joe