SQL select from array in JSON

Question

I have a table with a json field with the following json:

[
 {
     productId: '1',
     other : [
         otherId: '2'
     ]
 },
 {
     productId: '3',
     other : [
         otherId: '4'
     ]
 }
]

I am trying to select the productId and otherId for every array element like this:

select JSON_EXTRACT(items, $.items[].productId) from order;

But this is completely wrong since it takes only the first element in the array

Do I need to write a loop or something?

You can do this, but it will never be efficient. Whenever you find yourself wanting to look inside json data while still in the database, you should first look at including that data as part of the schema for the DB and changing the application to also set those fields at insert/update time. — Joel Coehoorn
– Joel Coehoorn, Commented Jun 28, 2021 at 14:29
@JoelCoehoorn I need this for migrating that existing data that is stored as JSON to a new table that I made — Thomas Britsom
– Thomas Britsom, Commented Jun 28, 2021 at 14:31
@ThomasBritsom Don't write inline SQL queries. Is not looking good and is not readable. 2nd thing, why you named table order? Why you use reserved word for table name? — Marko Ivkovic
– Marko Ivkovic, Commented Jun 28, 2021 at 14:33

Bill Karwin · Accepted Answer · 2021-06-28 15:08:10Z

1

First of all, the data you show is not valid JSON. It has multiple mistakes that make it invalid.

Here's a demo using valid JSON:

mysql> create table orders ( items json );
mysql> insert into orders set items = '[ { "productId": "1", "other": { "otherId": "2" } }, { "productId": "3", "other" : { "otherId": "4" } } ]'

mysql> SELECT JSON_EXTRACT(items, '$[*].productId') AS productIds FROM orders;
+------------+
| productIds |
+------------+
| ["1", "3"] |
+------------+

If you want each productId on a row by itself as a scalar value instead of a JSON array, you'd have to use JSON_TABLE() in MySQL 8.0:

mysql> SELECT j.* FROM orders CROSS JOIN JSON_TABLE(items, '$[*]' COLUMNS(productId INT PATH '$.productId')) AS j;
+-----------+
| productId |
+-----------+
|         1 |
|         3 |
+-----------+

This is tested in MySQL 8.0.23.

You also tagged your question MariaDB. I don't use MariaDB, and MariaDB has its own incompatible implementation of JSON support, so I can't predict how it will work.

answered Jun 28, 2021 at 15:08

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

Thomas Britsom Over a year ago

Yes I tagged MariaDB since I use MariaDB 10.4

Bill Karwin Over a year ago

Okay I've removed the mysql tag. You should not think of MySQL and MariaDB as compatible or workalike products anymore. MariaDB forked from MySQL in 2010, but they have both diverged since then. I'll leave my answer above in case it works for you or for some other reader, but I can't guarantee it works with MariaDB.

Collectives™ on Stack Overflow

SQL select from array in JSON

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related