Keyword

Reading JSON Format in K2 Tables

  • Joseph Rebele
  • Joseph Rebele's Avatar Topic Author
  • Offline
  • Junior Member
More
2 weeks 4 days ago - 2 weeks 4 days ago #179030 by Joseph Rebele
Reading JSON Format in K2 Tables was created by Joseph Rebele
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
Last edit: 2 weeks 4 days ago by Joseph Rebele.

Please Log in or Create an account to join the conversation.

  • Fotis
  • Fotis's Avatar
  • Online
  • Administrator
  • JoomlaWorks Support Team
More
2 weeks 4 hours ago #179056 by Fotis
Replied by Fotis on topic Reading JSON Format in K2 Tables
Can you paste the full code that queries the DB? Besides the direct query, I need to see HOW data is returned...

If you use & love K2, please take a moment to add your review and rate it
at the Joomla Extensions Directory: extensions.joomla.org/extension/k2/


IMPORTANT: Please search the forum before posting a question!

JoomlaWorks Support Team Member

Please Log in or Create an account to join the conversation.

  • Joseph Rebele
  • Joseph Rebele's Avatar Topic Author
  • Offline
  • Junior Member
More
1 week 6 days ago #179064 by Joseph Rebele
Replied by Joseph Rebele on topic Reading JSON Format in K2 Tables
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
Attachments:

Please Log in or Create an account to join the conversation.

  • Joseph Rebele
  • Joseph Rebele's Avatar Topic Author
  • Offline
  • Junior Member
More
1 week 7 hours ago #179086 by Joseph Rebele
Replied by Joseph Rebele on topic Reading JSON Format in K2 Tables
Fortis,

Have you had the opportunity to look at this query?

Thanks,
Joe

Please Log in or Create an account to join the conversation.

  • Fotis
  • Fotis's Avatar
  • Online
  • Administrator
  • JoomlaWorks Support Team
More
4 days 2 hours ago #179107 by Fotis
Replied by Fotis on topic Reading JSON Format in K2 Tables
The best option (at least for me) would be to retrieve all relevant content with SQL queries first and then use PHP to process the results in a more sane way. You can also skip retrieving any content from the extra field tables if you know the key of your extra field data.

If you use & love K2, please take a moment to add your review and rate it
at the Joomla Extensions Directory: extensions.joomla.org/extension/k2/


IMPORTANT: Please search the forum before posting a question!

JoomlaWorks Support Team Member

Please Log in or Create an account to join the conversation.


Powered by Kunena Forum