MySQL search json value by key in array

Question

I have an array of JSON objects and want to have a specific node returned. To simplify my problem, lets say the array could look like this:

[
    {"Race": "Orc", "strength": 14},
    {"Race": "Knight", "strength": 7},
    ...
]

And I want to know the strength of the knight for example. The function JSON_SEARCH, returns the path '$[1].Race' and with the path operator I could get the strength. Is there a way to combine those two, so I could do something like the following?

SELECT someFunc(myCol,'$[*].Race','Orc','$.strength') AS strength
FROM myTable

I am using MySQL 8.0.15.

Why don't you use normal columns and relation instead of json, probably faster lookups and you don't have to hassle with logic and magic. — Daniel W., Apr 23 '19 at 11:17
@DanFromGermany thanks for the edit. I don't use normal columns and relations because the actual data is something I don't know the structure of. The actual data is more like an json array of work supplies and I want to get the phone number. But the json data may contain normal stuff like a phone and a laptop but it may contain weird stuff like a bike or a nerf gun. Every type may only be included once but the entire process is working with a json array and rewriting without a fixed structure doesn't make sense at this point :( — DataVader, Apr 23 '19 at 11:28

score 6 · Accepted Answer · answered Apr 23 '19 at 14:12

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:

You need to do this every time you query the JSON data, or else create a VIEW to do it.
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.

Thanks. I hoped there was another way than doing JSON_TABLE() but apparently there isn't, besides drastically changing the business logic. — DataVader, Apr 23 '19 at 14:33

score 5 · Answer 2 · answered Jan 09 '21 at 08:29

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.

score 0 · Answer 3 · answered Apr 23 '19 at 13:22

0

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.

answered Apr 23 '19 at 13:22

Dave Stokes

775
4
10

Please have a look at how to format your answer to improve readability. – Daniel W. Apr 23 '19 at 13:59

MySQL search json value by key in array

3 Answers3

Linked

Related