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;
2
Answers
https://dbfiddle.uk/1SJR2xkU
You can achieve the result you want using
JSON_ARRAY_APPEND
, taking advantage of the fact that:and using a dummy path when you don’t want to add the object to the array.
Output for your sample data:
Demo on db-fiddle