MySQL construct nested json from two tables

Question

Quit struggling a while on this one.

In php I start with some users:

$uids = [1,2,3,4,5];

Then I create different possible week plannings:

$plannings = array
(
 1 => 
 array(
   'friday' => 'cook'
 ),
 2 => 
 array(
   'friday' => 'cook',
   'sunday' => 'play outside'
 ),
 3 => 
 array(
   'friday' => 'training',
   'sunday' => 'training'
 ),
 4 => 
 array(
   'friday' => 'meeting',
   'sunday' => 'brunch'
 ),
 5 => 
 array(
   'sunday' => 'rest'
 )
);

insert them in mysql:

foreach($plannings as $id => $details)
{
   INSERT INTO planning (id, data) VALUES ($id, json_encode($details));
}

after that, I assign randomly each user with different planning for the week

$week = [];
foreach($uids as $uid)
{
  $week [$uid] = rand(1,5) // which refers to the id of one of the plannings in the plannings array;
}

then I put that into another table saved as JSON

INSERT INTO week (data) VALUES (json_encode($week));

Now it comes, if I want to get the week planning into one array from the DB I can only come up with this dirty solution. for each user included in the week planning I make a new query to retrieve that specific planning

$week = SELECT data FROM week WHERE id = 1

$week = json_decode($week->data);
foreach($week as $uid => $planning_id)
{
 $planning = SELECT data FROM planning WHERE id = $planning_id
 $week[$uid] = json_decode($planning->data)
}

Now I am kind of silently hoping there could be a way doing this in one simple query using JOINS, JSON_EXTRACT, and other fancy methods in Mysql which I don't know of

thx pointing that out, should be json_encode... is merely pseudo code as I didn't insert the db wrapper class neither in order to keep this as readable as possible — Vermeersch Wouter
– Vermeersch Wouter, Commented Jul 4, 2021 at 13:21
What did you try using JOIN? The documentation has lost of examples, which cover this too! — Luuk
– Luuk, Commented Jul 4, 2021 at 13:23
post a minimal reproducible example wwith all tables and data, also tell us exactly which mysql Mariadb version you have — nbk
– nbk, Commented Jul 4, 2021 at 13:23

Indra Kumar S · Accepted Answer · 2021-07-04 14:48:34Z

2

If I understand your need correctly, You want to have an INNER JOIN ON values of a two different JSON from 2 different tables.

I assume you use MySQL 8+.

You requirement can be achieved by using JSON_TABLE functions

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

Extracts data from a JSON document and returns it as a relational table having the specified columns.

Lets create tables with a sample data (based on your php codes)

CREATE TABLE `week` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `data` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO week (data) VALUES ('{"1":2,"2":1,"3":2,"4":1,"5":4}');

CREATE TABLE `planning` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `data` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO planning (id, data) VALUES (1, '{"friday":"cook"}') ;
INSERT INTO planning (id, data) VALUES (2, '{"friday":"cook","sunday":"play outside"}') ;
INSERT INTO planning (id, data) VALUES (3, '{"friday":"training","sunday":"training"}') ;
INSERT INTO planning (id, data) VALUES (4, '{"friday":"meeting","sunday":"brunch"}') ;
INSERT INTO planning (id, data) VALUES (5, '{"sunday":"rest"}') ;

SELECT * FROM week will return

id	data
1	{"1":2,"2":1,"3":2,"4":1,"5":4}

And SELECT * FROM planning will return

id	data
1	{"friday":"cook"}
2	{"friday":"cook","sunday":"play outside"}
3	{"friday":"training","sunday":"training"}
4	{"friday":"meeting","sunday":"brunch"}
5	{"sunday":"rest"}

Now you want to have INNER JOIN on week.data.json_field's_value = planning.id

SELECT
    mytable.*,
    planning.data
FROM
    WEEK,
    JSON_TABLE(
        JSON_EXTRACT(WEEK.data, '$.*'),
        "$[*]" COLUMNS(plan_id INT PATH "$")
    ) mytable
INNER JOIN planning ON mytable.plan_id = planning.id

Result will be

plan_id	data
2	{"friday":"cook","sunday":"play outside"}
1	{"friday":"cook"}
2	{"friday":"cook","sunday":"play outside"}
1	{"friday":"cook"}
4	{"friday":"meeting","sunday":"brunch"}

db<>fiddle here

edited Jul 4, 2021 at 14:48

answered Jul 4, 2021 at 14:43

Indra Kumar S

2,9552 gold badges19 silver badges29 bronze badges

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

12 Comments

Reed Over a year ago

Heckin nice answer. I didn't know about JSON_TABLE. Super cool!

Vermeersch Wouter Over a year ago

That's great my friend! how would that translate to MySQL 5.7 ? Now it throws me the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( JSON_EXTRACT(week.data, '$.*'), "$[*]" COLUMNS( ' at line 5

Indra Kumar S Over a year ago

I dont know how it can be done in MySQL 5.7

Vermeersch Wouter Over a year ago

I see you use longtext instead of json type for the data col, is there a specific reason why you use the one over the other? I'll dig a little deeper on how fixing the error and make it work on Mysql 5.7. anyway thank you a lot for pointing me into the right direction!

Indra Kumar S Over a year ago

JSON is an alias for LONGTEXT

|

Collectives™ on Stack Overflow

MySQL construct nested json from two tables

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related