How to return JSON_ARRAY using an GROUP_CONCAT

Question

I've try return json object using "concat" and "group_concat" functions. Problem is that I need to use group_concat but I want to have a valid JSON structure. What I do wrong?

{"a":"a", "b": "b", "id": null}
[{"id": "123"}]

...

SELECT JSON_REPLACE((
                  SELECT JSON_OBJECT(
                             'a', 'a',
                             'b', 'b',
                             'id', null
                           )), '$.id', (
                  SELECT CONCAT(
                             '[', group_concat(JSON_OBJECT(
                          'id',
                          '123')),
                             ']'))
     )

result: {"a": "a", "b": "b", "id": "[{\"id\": \"123\"}]"}

expected: {"a": "a", "b": "b", "id": [{"id": "123"}]}

Barbaros Özhan · Accepted Answer · 2021-04-14 10:56:11Z

5

Extra CASTing AS JSON for the third argument of JSON_REPLACE fixes the issue :

SELECT JSON_REPLACE((
                  SELECT JSON_OBJECT(
                             'a', 'a',
                             'b', 'b',
                             'id', null
                           )), '$.id', 
                         CAST( CONCAT('[', GROUP_CONCAT(
                                            JSON_OBJECT('id', '123')),
                                      ']') AS JSON )) as "Result JSON"

Result :

{
  "a": "a",
  "b": "b",
  "id": [
    {
      "id": "123"
    }
  ]
}

Demo

edited Apr 14, 2021 at 10:56

answered Aug 29, 2019 at 8:49

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

3 Comments

Barbaros Özhan Over a year ago

but did you examine the resulting syntax after and before applying that ? Please consider the Demo

Madhur Bhaiya Over a year ago

You are right; Due to group_concat, it is getting converted to string and thus this double quote issue. +1

IkyryguJava Over a year ago

It was very helpful. I spent a lot of hours tried to resolve this issue. Thanks for involvement.

Ed Bangga · Accepted Answer · 2019-08-29 09:03:07Z

2

this is what you need JSON_ARRAYAGG

  SELECT JSON_REPLACE((SELECT JSON_OBJECT(
                             'a', 'a',
                             'b', 'b',
                             'id', null
                           )), '$.id', 
            (SELECT JSON_ARRAYAGG(JSON_OBJECT('id','123')))           
  )

if not using JSON_ARRAYAGG

  SELECT JSON_REPLACE((SELECT JSON_OBJECT(
                             'a', 'a',
                             'b', 'b',
                             'id', null
                           ))
            , '$.id'
            ,  (SELECT JSON_ARRAY(CAST(group_concat(JSON_OBJECT('id','123')) as json)))
   )

edited Aug 29, 2019 at 9:03

answered Aug 29, 2019 at 8:41

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

7 Comments

IkyryguJava Over a year ago

I forget to mention that is not possible I use Amazon aurora MySQL and my version doesn't have this function

IkyryguJava Over a year ago

yes. JSON_ARRAY is available. if I change without using "group_concat" function I get "subquery return more than 1 row".

Ed Bangga Over a year ago

you don't need subquery, remove select on the 2nd column

IkyryguJava Over a year ago

Is the main issue. In production code I have subquery and I need unjumble this JSON together. And the second subquery will return couple rows.

IkyryguJava Over a year ago

It was very helpful. I spent a lot of hours tried to resolve this issue. Thanks for involvement.

|

Collectives™ on Stack Overflow

How to return JSON_ARRAY using an GROUP_CONCAT

2 Answers 2

3 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related