How to dynamically provide key & values for JSON_OBJECT()?

Question

I am searching "FOR JSON PATH" (SQL Server) equivalent in MySQL and came across the following link - stackoverflow question

The most rated solution works perfectly but is it possible to provide the columns and values dynamically? I have over 20 columns and adding each key-value pair manually is hectic & not easy to maintain.

SQL Server Query-

SELECT u.user_id, u.first_name,(
SELECT f.*
FROM files f
WHERE f.is_active = 1 AND f.user_id = u.user_id
FOR JSON PATH) as files
FROM users u
WHERE u.user_id = 1

The output from the above query and the 1 generated using JSON_ARRAYAGG(JSON_OBJECT()) function is the same.

Sample data and desired results would help. – Gordon Linoff Jun 14 '21 at 10:24 — Gordon Linoff, Jun 14 '21 at 10:24

score 0 · Answer 1 · answered Jun 14 '21 at 10:27

0

You can do something like this:

SELECT u.user_id, u.first_name,
       (SELECT JSON_ARRAYAGG(JSON_OBJECT('col1', col1, 'col2', col2, . . . ))
        FROM files f
        WHERE f.is_active = 1 AND f.user_id = u.user_id
       ) as files
FROM users u
WHERE u.user_id = 1;

That is, you can construct the object explicitly from the columns in files and then aggregate those values into an array.

answered Jun 14 '21 at 10:27

Gordon Linoff

1,242,037
58
646
786

instead of specifying the columns & values explicitly inside JSON_OBJECT, is it possible to select all the columns & values from the table directly similar to a "select * from files" query. – Sibi Kandathil Jun 14 '21 at 10:40

xGeo · Answer 2 · 2021-06-14T13:31:57.760

For doing it explicitely typing the columns as per your comment on @Gordon Linoff's answer, you need to execute a dynamic query (PREPARED STATMENTS).

set @qs = (SELECT
    GROUP_CONCAT(COLS)
FROM (
  SELECT
    CONCAT(
      '"', 
      `COLUMN_NAME`,
      '",',
      `COLUMN_NAME`
    ) as COLS
  FROM `INFORMATION_SCHEMA`.`COLUMNS`
  WHERE `TABLE_SCHEMA` = 'test'
  AND `TABLE_NAME`='Files'
) t);


set @fin = CONCAT("SELECT u.user_id, u.first_name,
       (SELECT JSON_ARRAYAGG(JSON_OBJECT(",@qs,  "))
        FROM Files f
        WHERE f.is_active = 1 AND f.user_id = u.user_id
       ) as files
FROM Users u");

PREPARE dynamic_statement FROM @fin;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

See example fiddle here.

How to dynamically provide key & values for JSON_OBJECT()?

2 Answers2