1

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!

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.

3
  • do i need to install this module? – KUSHAL AGRAWAL Oct 22 '19 at 13:11
  • how can we do this using the code given in question? – KUSHAL AGRAWAL Oct 22 '19 at 13:11
  • As I explained in the answer, you can write the raw query and use the MySQL JSON query functions without trying to feed it through the Query object in the original question. – Kevin Oct 22 '19 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.