Search an array of JSON objects in MySQL

Question

I am trying to retrieve an element from a JSON array in MySQL using JSON_SEARCH based on multiple properties ("fromUnit" and "toUnit").

The array is as follows:

SET @unitConversions = '{
    "unitConversions": [
    {
    "fromUnit": "ounce",
    "toUnit": "cup",
    "amount": "10"
    },
    {
    "fromUnit": "ounce",
    "toUnit": "pound",
    "amount": "16"
    },
    {
    "fromUnit": "teaspoon",
    "toUnit": "ounce",
    "amount": "4"
    }
    ]
    }';

if I wanted to extract the element:

{
    "fromUnit": "ounce",
    "toUnit": "pound",
    "amount": "16"
    }

from this list, how can I do this.

So far I have tried:

SELECT JSON_SEARCH( @unitConversions, 'all', 'teaspoon');

this is fine for the whole list, but can this be used on multiple properties something like;

SELECT JSON_SEARCH( @unitConversions, 'all', '"fromUnit": "teaspoon", "toUnit":         "cup"');

Thanks in advance.

Answer 1

You can use JSON_TABLE to convert the json array of objects into objects then apply the where clause :

SELECT *
FROM JSON_TABLE(
         @unitConversionsJSON,
         "$.unitConversions[*]"
         COLUMNS(
           elem JSON PATH "$"
         )
       ) data
  WHERE elem->>'$.fromUnit' = 'ounce' and elem->>'$.toUnit' = 'cup'