MySql 8.0 - Filtered JSON query

Question

I have a JSON blob that I am trying to extract a value from, specifically, the Zip Code under the Rating Information section (Expected value = 90703). Does MySql 8 support JSON filter expressions?

JSON:

{
  "quote_number": null,
  "items": [
      {
          "annual_amount": 0.0,
          "pro_rata_amount": 0.0,
          "name": "Value Information",
          "categories": {
              "Use": "Single Family Detached",
              "Zip Code": "51431",
              "Floor Coverings": "Carpet"
          }
      },
      {
          "annual_amount": 0.0,
          "pro_rata_amount": 0.0,
          "name": "Rating Information",
          "categories": {
              "Number of Non-Weather Water Losses": "0",
              "Protection Class": "2",
              "Zip Code": "90703",
              "Special Hazard Interface Area": "N"
          }
      }
  ],
  "total": {
      "annual_fees": 0.0,
      "annual_premium": 9.0
  },
  "policy_id": null
}

Path: $.items[?(@.name=="Rating Information")].categories.Zip Code

The path appears to be correct as I get data when testing via this site: https://jsonpath.com/

If MySql doesn't support JSON filtering, what is the recommended work around?

Joe

Answer 1

MySQL 8.0 and 8.1 does not implement full support for jsonpath expressions. For example, it does not support filtering expressions. The limited support for jsonpath is documented here: https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax

The solution in MySQL for filtering JSON data is to use JSON_TABLE().

I tested your data:

set @j = '{ ...your json... }';

select * from json_table(@j, '$.items[*]' columns(
name text path '$.name',
zip_code text path '$.categories."Zip Code"'
)) as j;

+--------------------+----------+
| name               | zip_code |
+--------------------+----------+
| Value Information  | 51431    |
| Rating Information | 90703    |
+--------------------+----------+

You can then put a WHERE clause on the query to get the one you want.

Read more about JSON_TABLE(): https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html