JSON_Extract error: Invalid JSON text in argument 1 to function json_extract: "Missing a name for object member."

Question

I'm trying to extract "name" from JSON column "Value":

Table t:

id	Value
1	[{'id': 116298, 'name': 'Data Analysis', 'language': 'en'}, {'id': 5462, 'name': 'Visualization', 'language': '00'}]

My query is:

select 
json_extract(t.value,'$name')
from t

Also tried:

select 
JSON_SEARCH(t.value, 'all', 'name')
from t

The error I get is:

Data truncation: Invalid JSON text in argument 1 to function json_extract: "Missing a name for object member." at position 2.

What am I missing? Appreciate your help!

Answer 1

Check your JSON value. I copied your Value to a JSON validator and it does not like single quotes. So if I run the following I get no results:

SET @mapJSON = "[{'id': 116298, 'name': 'Data Analysis', 'language': 'en'}, {'id': 5462, 'name': 'Visualization', 'language': '00'}]";
SELECT JSON_SEARCH(@mapJSON, "all", "name") as t WHERE JSON_VALID(@mapJSON)=1; -- no result returned

The other problem is that JSON_SEARCH will search on a value, so if you run the following SQL you will get ["$[0].name", "$[2].name"] (being the first object and third object in the array with a name key with value matching "Data Analysis").

SET @mapJSON = '[{"id": 116298, "name": "Data Analysis", "language": "en"}, {"id": 5462, "name": "Visualization", "language": "00"}, {"id": 988, "name": "Data Analysis", "language": "es"}]';
SELECT JSON_SEARCH(@mapJSON, "all", "Data Analysis") as t WHERE JSON_VALID(@mapJSON)=1

Since your Value is an array of objects:

[
{'id': 116298, 'name': 'Data Analysis', 'language': 'en'}, 
{'id': 5462, 'name': 'Visualization', 'language': '00'}
]

...each Value should be a single object such as:

{'id': 116298, 'name': 'Data Analysis', 'language': 'en'}

...in which case you should get "Data Analysis" when you run:

SET @mapJSON = '{"id": 116298, "name": "Data Analysis", "language": "en"}';
SELECT json_extract(@mapJSON,'$.name') as t WHERE JSON_VALID(@mapJSON)=1

[FYI: I'm using MySQL v8]

Answer 2

You are using the wrong syntax:

select value->"$.name"
from t

as explained here

You can add a where condition like this

select value->"$.name"
from t
WHERE JSON_EXTRACT(value, "$.name") = 'Meital'

The -> operator serves as an alias for the JSON_EXTRACT() function when used with two arguments, a column identifier on the left and a JSON path on the right that is evaluated against the JSON document (the column value). You can use such expressions in place of column identifiers wherever they occur in SQL statements.