MySQL search json value by key in array

You're essentially meaning to apply selection and projection to the array elements and object fields of your JSON document. You need to do something like a WHERE clause to select a "row" within the array, and then do something like picking one of the fields (not the one you used in your selection criteria).

These are done in SQL using the WHERE clause and the SELECT-list of columns, but doing the same with JSON isn't something you can do easily with functions like JSON_SEARCH() and JSON_CONTAINS().

The solution MySQL 8.0 provides is the JSON_TABLE() function to turn a JSON document into a virtual derived table — as though you had defined conventional rows and columns. It works if the JSON is in the format you describe, an array of objects.

Here's a demo I did by inserting your example data into a table:

create table mytable ( mycol json );insert into mytable set mycol = '[{"Race": "Orc", "strength": 14}, {"Race": "Knight", "strength": 7}]';SELECT j.* FROM mytable, JSON_TABLE(mycol,   '$[*]' COLUMNS (    race VARCHAR(10) PATH '$.Race',     strength INT PATH '$.strength'  )) AS j;+--------+----------+| race   | strength |+--------+----------+| Orc    |       14 || Knight |        7 |+--------+----------+

Now you can do things you normally do with SELECT queries, like selection and projection:

SELECT j.strength FROM mytable, JSON_TABLE(mycol, '$[*]'   COLUMNS (    race VARCHAR(10) PATH '$.Race',     strength INT PATH '$.strength'  )) AS j WHERE j.race = 'Orc'+----------+| strength |+----------+|       14 |+----------+

This has a couple of problems:

1. You need to do this every time you query the JSON data, or else create a VIEW to do it.

2. You said you don't know the attribute fields, but to write a JSON_TABLE() query, you must specify the attributes you want to search and project in your query. You can't use this for totally undefined data.

I've answered quite a number of similar questions about using JSON in MySQL. I've observed that when you want to do this sort of thing, treating a JSON document like a table so you can apply condition in the WHERE clause to fields within your JSON data, then all your queries get a lot more difficult. Then you start feeling like you would have been better off spending a few minutes to define your attributes so you could write simpler queries.

There is a way to solve this in SQL 5.7. I will go step by step in composing the solution. The goal is to find the strength of the knight.

I am going to use the same sample table as previous post:

create table mytable ( mycol json );insert into mytable set mycol = '[{"Race": "Orc", "strength": 14}, {"Race": "Knight", "strength": 7}]';

First, get an array of only the races.

select json_extract(mycol, '$[*].Race') from mytable;+----------------------------------+| json_extract(mycol, '$[*].Race') |+----------------------------------+| ["Orc", "Knight"]                |+----------------------------------+

Then, search for the Knight in this array (and unquote it).

select json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight')) from mytable;+------------------------------------------------------------------------------+| json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight')) |+------------------------------------------------------------------------------+| $[1]                                                                         |+------------------------------------------------------------------------------+

Having found the index, get this element from the array.

select json_extract(mycol, json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight'))) from mytable;+---------------------------------------------------------------------------------------------------+| json_extract(mycol, json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight'))) |+---------------------------------------------------------------------------------------------------+| {"Race": "Knight", "strength": 7}                                                                 |+---------------------------------------------------------------------------------------------------+

Then get the strength of this element.

select json_extract(json_extract(mycol, json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight'))), '$.strength') as strength from mytable;+----------+| strength |+----------+| 7        |+----------+

You can repeat this on other fields to create other columns.

Look at JSON_CONTAINS (https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains) and us it in your WHERE clause to identify the records that match your criteria.