convert MySQL table aggregating multiple rows into JSON object

Question

I am trying to convert a table, where I want to aggregate multiple rows into one JSON column. The old table has a primary key consisting of multiple columns, and two columns A and B. A should become the field name and B the value in the new table.

This is my Query so far:

INSERT INTO new_table (pk1, pk2, pk3, json_column)
SELECT DISTINCT pk1,
                pk2,
                pk3,
                JSON_INSERT(SELECT JSON_ARRAYAGG(JSON_OBJECT(A, B)) from old_table
                            WHERE
                             pk1 = old.pk1 AND pk2 = old.pk2 AND pk3 = old.pk3)
FROM old_table AS old;

My IDE ist telling me, that "old_table" cannot be resolved in the JSON_INSERT-Statement, but I cannot figure out why.

nbk · Accepted Answer · 2022-06-27 09:45:24Z

2

JSON_INSERT is actually the way to insert json into jsons, but you don't need to, so skip it.

But your query would be right if you do

INSERT INTO new_table (pk1, pk2, pk3, json_column)
SELECT DISTINCT pk1,
                pk2,
                pk3,
                JSON_INSERT((SELECT JSON_ARRAYAGG(JSON_OBJECT(A, B)) from old_table
                            WHERE
                             pk1 = old.pk1 AND pk2 = old.pk2 AND pk3 = old.pk3))
FROM old_table AS old;

but actually you need only

INSERT INTO new_table (pk1, pk2, pk3, json_column)
SELECT DISTINCT pk1,
                pk2,
                pk3,
                (SELECT JSON_ARRAYAGG(JSON_OBJECT(A, B)) from old_table
                            WHERE
                             pk1 = old.pk1 AND pk2 = old.pk2 AND pk3 = old.pk3)
FROM old_table AS old;

edited Jun 27, 2022 at 9:45

answered Jun 26, 2022 at 21:57

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

convert MySQL table aggregating multiple rows into JSON object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related