How do I search nested json query in mysql?

Question

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

Ed Bangga · Accepted Answer · 2019-11-19 08:31:43Z

1

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

edited Nov 19, 2019 at 8:31

answered Nov 19, 2019 at 7:42

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Aiden Over a year ago

that's my point, my json is invalid it works well using with replace~!

Aiden Over a year ago

another question~! how can i extracted iterated key,values ? id[0], count[0] id[1], count[1] ~~~~~~~~~~ id[n], count[n]

Ed Bangga Over a year ago

@Aiden, this should be in separate question, if there were 5 people who answered this question, they will be all invalid.

Aiden Over a year ago

@metal this ismy new question. please help me. stackoverflow.com/questions/58932899/…

Ed Bangga Over a year ago

@Aiden, sorry, but i'm not sure if you are accepting answers or at least vote for the efforts done.

Abhisek Mishra · Accepted Answer · 2019-11-19 07:36:32Z

0

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}]}}';

edited Nov 19, 2019 at 7:36

answered Nov 19, 2019 at 7:08

Abhisek Mishra

2693 silver badges15 bronze badges

1 Comment

Aiden Over a year ago

result is null , no way to retrieve 530 ?

Collectives™ on Stack Overflow

How do I search nested json query in mysql?

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related