Populate JSON field with mapped values

Question

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)

why do you want to do that ? and basically you only need GROUP_CONCAT for that and simple string manipulation — nbk, Apr 23 '23 at 09:56
also always a good read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, Apr 23 '23 at 10:04

score 2 · Answer 1 · answered Apr 23 '23 at 14:56

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

score 1 · Answer 2 · answered Apr 23 '23 at 10:15

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

Populate JSON field with mapped values

2 Answers2