0

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:

i want to retrieve iterated key, value.

The following result has the position of the data

select json_extract(replace(replace(replace(trim(@jsn_string), '\"[', '['), ']\"', ']'), '\\"', '\"'),'$.body.items[0].id') as id,
json_extract(replace(replace(replace(trim(@jsn_string), '\"[', '['), ']\"', ']'), '\\"', '\"'),'$.body.items[0].count') as count;

result is id count 5 530

but i want all of array, such as below

id[0] count[0], id[1] count[1], id[n] count[n]

Improve this question
3
  • ideally upgrade to MySQL 8 where you have JSON_TABLE().. Otherwise look into a answer which i posted before to simulate JSON_TABLE() on MySQL 5.7 and below.. Commented Nov 19, 2019 at 11:22
  • hmm no way to search nested array easily??? Commented Nov 19, 2019 at 13:07
  • See Why should I provide a Minimal Reproducible Example for a very simple SQL query? ... As i might have misread that JSON structure and what you are trying to get.. Pretty you need to deal with (dynamic) numeric items here which involves writting tricky SQL.. Commented Nov 19, 2019 at 13:26
Related questions
1
search in json values from mysql tables
112
How to search JSON array in MySQL?
10
How to search nested JSON in MySQL
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.