Question 39983 in Mysql Question posted in
The official MySQL documentation can be found here.

Populate JSON field with mapped values – Mysql

I have two tables A and B.

A (id int PRIMARY_KEY, json_field JSON)
B (a_id FOREIGN_KEY, value int)

The relationship is One To Many. So A.id might occur several times in B.a_id. So the tables could look something like this:

A:
id ___ json_field
1       null
2       null

B:
a_id ___ value
1         100
1         101
2         200
2         201

Now I need a query that puts the ID-matching values into the json_field as an array.

So the result should look something like:

A:
id ___ json_field
1       [100, 101]
2       [200, 201]

We can assume that the json_fields are initially empty.

I guess this could be done with a subquery, but I can’t quite figure out how.

Update A
Set json_field = CREATE JSON FROM RESULTSET (
  SELECT value
  FROM A, B
  WHERE A.id = B.a_id)
Tags:

2

Answers


  1. 0

    The simplest way will be to use String Functions

    but usually you don’t want to store data twice in a database, you can as you see below always create the output wanted add every time

    Also you should read the thread about storing delimited data

    if the values must be in a fixed order your B table needs a sorting column so that GROUP_CONCAT can sort them in the wanted order

    UPDATE A
  INNER JOIN  (
SELECT 
  a_id,
CONCAT('[',GROUP_CONCAT(`value` ),']') val
FROM B
GROUP BY a_id) t2 ON A.id = t2.a_id
SET json_field = t2.val

    Rows matched: 2  Changed: 2  Warnings: 0

    SELECT id,JSON_PRETTY(json_field) FROM A
    id JSON_PRETTY(json_field)
    1 [
      100,
      101
    ]
    2 [
      200,
      201
    ]

    fiddle

    Login or Signup to reply.
  2. 0

    Try using json_arrayagg:

    select a_id, json_arrayagg(value) as json_field
from B
group by a_id

    Output:

    a_id    json_field
1       [100, 101]
2       [200, 201]

    To update the A table we could use an update with join or with a correlated sub-query as the following:

    update A
set A.json_field =
(
  select json_arrayagg(value) 
  from B
  where A.id = B.a_id
)

    demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search