MySQL select field from array of JSON objects

Question

So i have in my database a column = foo_ids and its content is as follows:
[{"id": "432"}, {"id": "433"}]

question is what query will give me back as a result all of the id's in this field?

out of several attempts my last one was
SELECT JSON_EXTRACT(foo_ids,'$.id') FROM foo_table but clearly it didn't work...any idea?

mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.3 — yariv bar
– yariv bar, Commented Aug 14, 2017 at 9:25
Unless otherwise indicated, the JSON functions were added in MySQL 5.7.8., see 12.16 JSON Functions. — wchiquito
– wchiquito, Commented Aug 14, 2017 at 9:46

83N · Accepted Answer · 2017-08-14 09:44:34Z

7

If you just want to return an array of the ID's you could do this:

SELECT JSON_EXTRACT(foo_ids, '$**.id') FROM foo_table;

The * wildcard evaluates each json object in an array.

answered Aug 14, 2017 at 9:44

83N

2841 silver badge6 bronze badges

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

3 Comments

CaptRisky Over a year ago

Is there a way to return the array where id = 432 for example?

83N Over a year ago

Yes, by doing something along the lines of SELECT JSON_EXTRACT(foo_ids, '$**.id') FROM foo_table WHERE JSON_CONTAINS(foo_ids, '{ "id" : "432" }'); - check out the MySQL docs here

CaptRisky Over a year ago

This only works where the id is a string and not an int as such. '{ "id" : "432" }' will work but '{ "id" : 432 }' won't as it returns all items

Collectives™ on Stack Overflow

MySQL select field from array of JSON objects

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related