CODEWITHSUNDEEP
phpmysql
Vermeersch Wouter
Vermeersch Wouter

Reputation: 63

MySQL construct nested json from two tables

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

Upvotes: 1

Views: 176

Answers (1)

Indra Kumar S
Indra Kumar S

Reputation: 2934

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

Upvotes: 2

Related Questions