CODEWITHSUNDEEP
mysqljsonnested
Aiden
Aiden

Reputation: 27

How do I search nested json query in mysql?

I am using MySQL 5.7+ with the native JSON data type.

Sample data:

set @jsn_string='{\"body\": {\"items\": \"[{\\\"count\\\":530,\\\"id\\\":5},{\\\"count\\\":1,\\\"id\\\":519},{\\\"count\\\":209,\\\"id\\\":522},{\\\"count\\\":0,\\\"id\\\":3004}] \"}}';

Questions: the correct answer is 530

The following query has the position of the data 

select json_extract(@jsn_string,'$.body.items[0].id[0]');

but the result is : null

Upvotes: 0

Views: 194

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

we can use json_unquote to remove those double quotes in items[0]

set @jsn_string='{\"body\": {\"items\": \"[{\\\"count\\\":530,\\\"id\\\":5},{\\\"count\\\":1,\\\"id\\\":519},{\\\"count\\\":209,\\\"id\\\":522},{\\\"count\\\":0,\\\"id\\\":3004}] \"}}';


select json_extract(json_unquote(json_unquote(json_extract(@jsn_string, '$.body.items[0]')))
    ,'$[0].count');

see dbfiddle

Upvotes: 1

Abhisek Mishra
Abhisek Mishra

Reputation: 269

As per your sample JSON, id is not an array. So id[0] wont work As I understand from your question, you want to retrieve 530. For this you can use the following.

select json_extract(@jsn_string,'$.body.items[0].count');

Please let me know if I understood it incorrectly.

Edit - 1: I think your json is invalid. Array should not be wrapped in braces. Try this

@jsn_string='{\"body\": {\"items\": [{\\\"count\\\":530,\\\"id\\\":5},{\\\"count\\\":1,\\\"id\\\":519},{\\\"count\\\":209,\\\"id\\\":522},{\\\"count\\\":0,\\\"id\\\":3004}]}}';

Upvotes: 0

Related Questions