How to extract and flatten and array that is inside of JSON objects in MySQL 8?

Question

Given some json documents like this:

{
   "_id":"00006073",
    "subscribersIds":[
      170968,
      225647
   ]
}
-----------------------------------
{
   "_id":"00006072",
   "subscribersIds":[
      170968
   ]
}
--------------------------------
{
   "_id":"00006074,
   "subscribersIds":[
      228195,
      225647
   ]
}

Do you know how can I get a list of the subscribersIds, without repetition? The result should be something like this 170968, 225647, 228195, because I will need to use the result of this query as a condition of another one.

For Couchebase there is the "UNNEST" command that does it, however I didn't find the properly way to do it in MySQL 8, as SELECT DISTINCT doc ->> '$.subscribersIds[*]' FROM customers will return [170968,225647],[170968],[228195,225647]

Thanks in advance!

Bill Karwin · Accepted Answer · 2021-05-27 21:11:01Z

2

mysql> select * from mytable;
+----------+------------------+
| _id      | subscriberIds    |
+----------+------------------+
| 00006072 | [170968]         |
| 00006073 | [170968, 225647] |
| 00006074 | [228195, 225647] |
+----------+------------------+

mysql> select j.subscriberId from mytable, 
  json_table(mytable.subscriberIds, '$[*]' columns (subscriberId int path '$')) j;
+--------------+
| subscriberId |
+--------------+
|       170968 |
|       170968 |
|       225647 |
|       228195 |
|       225647 |
+--------------+

mysql> select distinct j.subscriberId from mytable,
  json_table(mytable.subscriberIds, '$[*]' columns (subscriberId int path '$')) j;
+--------------+
| subscriberId |
+--------------+
|       170968 |
|       225647 |
|       228195 |
+--------------+

This is a fairly complex query to write every time you want to get the set of distinct subscriberIds.

It would be much easier if you did not use JSON at all, but stored the id's in a normalized manner, one per row in a second table.

mysql> create table mySubscribers (_id char(8), subscriberId int, primary key (_id, subscriberId));

mysql> insert into mySubscribers (_id, subscriberId) select _id, subscriberId from mytable, json_table(subscriberIds, '$[*]' columns (subscriberId int path '$')) j;
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from mySubscribers;
+----------+--------------+
| _id      | subscriberId |
+----------+--------------+
| 00006072 |       170968 |
| 00006073 |       170968 |
| 00006073 |       225647 |
| 00006074 |       225647 |
| 00006074 |       228195 |
+----------+--------------+

mysql> select distinct subscriberId from mySubscribers;
+--------------+
| subscriberId |
+--------------+
|       170968 |
|       225647 |
|       228195 |
+--------------+

edited May 27, 2021 at 21:11

answered May 27, 2021 at 20:55

Bill Karwin

567k87 gold badges710 silver badges870 bronze badges

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

3 Comments

Jeff Mira Over a year ago

Thank you! Actually I'm working on the migration from Couchbase do MySQL 8 of a project, and unfortunately I cant reorganize the documents structure now (perhaps in the future), that is why I'm having to do this kind of query. Thanks a lot for your help!

ysth Over a year ago

do you prefer comma-join over explicit 'cross join' for json_table? I'm so used to thinking of it as code smell but I can see why saying 'cross join' doesn't quite fit something like json_table where there is a join criterion, just not in an explicit on clause. (so happy json_table is in mariadb 10.6 so I can get more experience with it)

Bill Karwin Over a year ago

If it were production code, I would use cross join, just to remain consistent in my habit of using SQL-92 syntax in all cases. I was using the SQL-89 style (comma-join) here just to be brief.

Collectives™ on Stack Overflow

How to extract and flatten and array that is inside of JSON objects in MySQL 8?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related