Question 268654 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Construct JSON_ARRAY with conditional second object

I have a simple sample table:

f1 f2
1 2
1 3

I want to create a JSON array of each line – BUT, I do not want to include the second object if its value is 3.

So I would like the results to be:

[{'num',1},{'num',2}]
[{'num',1}]

If I try this:

select JSON_ARRAY(JSON_OBJECT('num',f1),
                if(f2<>3,JSON_OBJECT('num',f2),JSON_OBJECT())                     
                  ) from sample_table;

its close, but it places an empty object instead of nothing:

[{"num": 1}, {"num": 2}]
[{"num": 1}, {}]

Is there a better way to handle?
Else, I suppose a 2nd option, is there an easy way to remove empty objects after the fact (like a wrapper around the JSON_ARRAY function)

I suppose if I place the IF at the start, that can work.. but is there a slicker way?

select if(f2<>3, JSON_ARRAY(JSON_OBJECT('num',f1),JSON_OBJECT('num',f2)),
                        JSON_ARRAY(JSON_OBJECT('num',f1))                     
                  ) from sample_table;
Tags:

2

Answers


  1. 0 
    SELECT CONCAT( '[',
               CONCAT_WS( ',',
                          JSON_OBJECT('num',f1),
                          CASE WHEN f2<>3 THEN JSON_OBJECT('num',f2) END
                          ),
               ']'
               )
FROM sample_table

    https://dbfiddle.uk/1SJR2xkU

    Login or Signup to reply.
  2. 0

    You can achieve the result you want using JSON_ARRAY_APPEND, taking advantage of the fact that:

    Pairs for which the path does not identify any value in the JSON document are ignored

    and using a dummy path when you don’t want to add the object to the array.

    select JSON_ARRAY_APPEND(
  "[]",
  '$',
  JSON_OBJECT('num',f1),
  CASE WHEN f2<>3 THEN '$' ELSE '$.dummy' END, 
  JSON_OBJECT('num',f2)
) AS result
FROM sample_table;

    Output for your sample data:

    result
    [{"num": 1}, {"num": 2}]
    [{"num": 1}]

    Demo on db-fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search