Fetch value from JSON

Question

I have a field in drupal "field_xyz". it stores json values. JSON looks like this:

{"Id_-_SiteWasteProductAction":"34638393224", "Postcode": "CSF 12CF"}

I want to fetch postcode value from this json using mysql query

i used

$query = $connection->query("SELECT @@sql_mode"); //for group by (php 7.0)
$query = $connection->query("set @@sql_mode=''"); //for group by (php 7.0)
$query = $connection->select('node_revision', 'n');
$query->fields('n', ['vid', 'nid']);
$query->fields('shad', ['field_xyz_value']);
$query->join('node__field_xyz', 'shad', 'n.vid = shad.revision_id'); 

i fetch postcode value from url-

   $filter_postcode_value = \Drupal::request()->query->get('filter_postcode_value');//postcode

and i use this condition

   if(!empty($filter_postcode_value)){
          $query->condition('an.field_xyz_value->"$.Postcode"', '%' . db_like($filter_postcode_value) . '%' ,'LIKE');   
     }

output is coming out as-

    Column not found: 1054 Unknown column 'shad.field_sheet_all_data_value.Postcode

Please help!

Answer 1

If I recall, you need to use the json_ specific query functions to query within a tuple.

There is an example query on the JSONB module page demonstrating this. This module is not required, but does have an example of how to write a db_query:

If you wanted to query for JSON records that were in the 2005-2006 timeframe for high school graduates, you could do the following:

$results = db_query("SELECT point
      FROM (SELECT jsonb_array_elements(field_jsondata_value) point FROM {node__field_jsondata}) points
      WHERE point->'DataType' = :type
        AND point->'TimeFrame' = :frame
        AND point->'Education' = :education",
      array(':type' => '"Percent"', ':frame' => '"2005-2006"', ':education' => '"High school graduate"'));

You would receive the fourth object only. The function jsonb_array_elements expands data at runtime to query data in this manner.

I don't think Drupal core supports the usage of these functions outright, maybe it does, but I accomplished this in both Drupal 7 and 8 with db_query.