自定义查询中的Joomla 3 Zoo组件JSON提取

Question

I am using zoo component for items and just moved Joomla from 1.5 to 3 version and Zoo component changed their format to save items

On the Previous version, zoo component was using XML format to save the data in the database and now in the new version, they are using JSON format.

Now, let me come to the issue:

I have developed custom module for fetching data where the query is like:

SELECT DISTINCT id, name FROM #__zoo_item WHERE application_id=1 and state=1 and type='abo' AND ExtractValue(elements,'/elements/relateditems[@identifier="9264aa8aa-6b39-4ecc-969a-f17da2688a5c"]/item')=24 ORDER BY ExtractValue(elements,'/elements/radio[@identifier="3dd960814e-b3f9-4ca2-abdb-070d0f51e7b9"]/option') ASC, FIELD(ExtractValue(elements,'/elements/radio[@identifier="86c0c108-8a31-48b9-badb-857ss7f359c1b7"]/option'),'ja','regal','ntein') ,ExtractValue(elements,'/elements/radio[@identifier="3960814e-b3f9-4ca2-abdb-070d0f51epp7b9"]/option') DESC

Here in the query used ExtractValue function of MySQL to match the extracted value from the XML.

Here is the XML format:

<?xml version="1.0" encoding="utf-8"?>
<elements>
<select identifier="9d534d17-d6d9-4671-a7d1-0b1f8ec71f07">
  <option><![CDATA[salt]]></option>
</select>
<relateditems identifier="9264aa8aa-6b39-4ecc-969a-f17da2688a5c">
  <item><![CDATA[24]]></item>
</relateditems>
<text identifier="b44baf4c-bbb0-4410-a12c-23ec3b7ba5d6">
  <value><![CDATA[]]></value>
</text>
<text identifier="c3903e1e-bc09-4708-bf8f-dc765a84ccd4">
  <value><![CDATA[]]></value>
</text>
<image identifier="4079ccd8-04df-41fd-b41e-52c23351a215">
  <file><![CDATA[test.png]]></file>
  <title/>
  <link/>
  <target/>
  <rel/>
  <lightbox_image/>
  <width><![CDATA[100]]></width>
  <height><![CDATA[80]]></height>
</image>
<radio identifier="86c0c108-8a31-48b9-badb-857ss7f359c1b7">
  <option><![CDATA[ja]]></option>
</radio>
<radio identifier="3960814e-b3f9-4ca2-abdb-070d0f51epp7b9">
  <option><![CDATA[0]]></option>
</radio>
</elements>

Now, Issue is how can I use a query which check matched extracted value in JSON format. Also, MySQL version is 5.5 so latest 5.7 JSON Functions are not working.

Here is the JSON Format:

{
    "9264aa8aa-6b39-4ecc-969a-f17da2688a5c":  {
        "item":  {
                "0": "8665"
        }
    },
        "50b019d0-6e30-4a27-a46c-68dbe5ade18b":  {
        "0":  {
            "value": "GRUNDGEB\u00dcHR: CHF 59.00|
ANRUFE: Unlimitiert in der Schweiz|
SMS\/MMS: Unlimitiert in der Schweiz|
INTERNET 4G: Unlimitiert in der Schweiz|"
        }
    },
    "3dd960814e-b3f9-4ca2-abdb-070d0f51e7b9":  {
        "option":  {
            "0": "salt"
        },
        "select": "1"
    },
    "26112e4c-83c5-4e38-806b-fccfb7f8aa69":  {
        "votes": 0,
        "value": 0
    },
}

Anyone has the idea how to check in JSON data? Any MySQL function work in this?

Answer 1

<?php
$db = JFactory::getDbo();   
 $query = $db->getQuery(true);
 $query->select($db->quoteName(array('64ccc68f-4af3-4c02-8c0f-8ec5977bacb2')))
  ->from($db->quoteName('#__zoo_item'))
  ->where($db->quoteName('application_id') . ' = 1 AND '. 
          $db->quoteName('state')  . ' = 1 AND '. 
          $db->quoteName('type')  . ' = ' $db->quoteName('handy'));

  $db->setQuery($query);  
  $results = $db->loadObjectList(); 
?>