1

I'm trying to build a JSON object with some data that I need aggregated. I first aggregate values into an array, and then I aggregate them into a json object. I have not been able to figure out how to prevent it from turning the JSON array into a string.

Here's an example, which mimics my schema, and shows the problem:

select
    rd.r,
    json_objectagg(rd.f, rd.r_data) as rdata
from (
  select
    r,
    f,
    CASE WHEN (count(v) > 1) THEN json_arrayagg(v) ELSE v END as r_data
  from (
    select 1 as r, 1 as f, 1 as v
    union all
    select 1 as r, 2 as f, 'a string' as v
    union all
    select 1 as r, 2 as f, 3 as v
    union all
    select 2 as r, 1 as f, 1 as v
    union all
    select 2 as r, 2 as f, 2 as v
    union all
    select 2 as r, 2 as f, '2023-01-01' as v
    union all
    select 3 as r, 1 as f, 1 as v
    union all
    select 3 as r, 2 as f, 2 as v
    union all
    select 3 as r, 2 as f, 'true' as v
  ) row_data
    group by row_data.r, row_data.f
  order by row_data.r, row_data.f
) rd
GROUP BY rd.r

The json generated is as follows: {"1":"1","2":"["a string", "3"]"}

Notice the array is quoted, making it not an array, but a string that looks like an array. How do I get it to be an actual array?

I've tried some different approaches, but they all result in the JSON array being treated as a string.

shakeman
  • 21
  • 2
  • 1
    I deleted my answer. Please don't ask people to answer and then tell them that your data is really something else than what is shown in your question. Show the real problem to be solved. – Bill Karwin Feb 03 '23 at 18:54
  • 1
    My apologies. I was trying to keep it as simple as possible while retaining the necessary elements. My lack of MySQL knowledge made me make a poor assumption. I have updated the question to better reflect the possible values in the data. – shakeman Feb 03 '23 at 19:04

0 Answers0