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.

Answer 1

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.