Skip to Main Content
  • Questions
  • Adding an optional json array to a json object

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Don.

Asked: May 27, 2020 - 3:32 pm UTC

Answered by: Chris Saxon - Last updated: June 03, 2020 - 10:55 am UTC

Category: SQL - Version: 12c

Viewed 100+ times

You Asked

I have a parent table and child table with a 1 to 0-many relationship. I need to create a json object that includes parent data and a list of child table data. The goal is an object that includes: "child":[] when there is no matching data in a child table.

If I do a simple outer join and group the parent data, I get: ...,"child":[{"CC":null,"XX":null}] (ex 1).

If I do an outer join to a subquery where I do the aggregation, I get: ...,"child":null (ex 2) which is better, but still not what was requested.

In the third I test for null in a case statement, and in the fourth I use a coalesce function to replace null with []. These result in extra quotes or backslash characters, making the json object invalid or incorrect.

Ex 5 is a kludge that is almost too awful to think about, but it "works."

Using json functions is new to me. Am I missing something simple?


with LiveSQL Test Case:

and we said...

I think you need to do a bit of fiddling to get the output you want. But there is a better way than using replace.

The ABSENT ON NULL clause excludes key with null values:

select json_object ( 
         'this' value null 
         absent on null 
       ) jo
from   dual;

JO   
{}


If you use this you'll get an array containing an empty object:

select p.pk, 
    json_object ( 
      'child' value json_arrayagg ( 
        json_object( 
          'CC' value c.CC,  
          'XX' value c.XX 
          absent on null
        ) 
      ) 
    ) as J
from MY_PARENT_TABLE p 
left join MY_CHILD_TABLE c  
    on c.PK = p.PK 
group by p.PK, p.TNAME, p.DEPT;

PK   J                                                                   
    1 {"child":[{"CC":20,"XX":22},{"CC":40,"XX":44},{"CC":30,"XX":33}]}    
    2 {"child":[{}]}  


Close to what you want, but not quite...

To return an empty array, you could check if JSON_object returns an empty object. And return null instead if it does.

Giving:

select p.pk, 
    json_object ( 
      'child' value json_arrayagg ( 
        case json_object( 
            'CC' value c.CC,  
            'XX' value c.XX 
            absent on null
          ) 
          when '{}' then null
          else json_object( 
            'CC' value c.CC,  
            'XX' value c.XX 
            absent on null
          ) 
        end 
        absent on null
      ) 
    ) as J
from MY_PARENT_TABLE p 
left join MY_CHILD_TABLE c  
    on c.PK = p.PK 
group by p.PK, p.TNAME, p.DEPT;

PK   J                                                                   
    1 {"child":[{"CC":20,"XX":22},{"CC":40,"XX":44},{"CC":30,"XX":33}]}    
    2 {"child":[]}  


I think that's the best you can manage at the moment.

and you rated our response

  (5 ratings)

Reviews

May 28, 2020 - 3:24 pm UTC

Reviewer: Don Simpson from Tucson, AZ, USA

That's great! Thanx so much.

I simplified it a little like this:
select json_object ( 
    'child' value json_arrayagg ( 
        case when c.PK is null 
            then null
            else json_object( 
                'CC' value c.CC,  
                'XX' value c.XX 
                ) 
        end 
        absent on null
      ) 
    ) as J
from MY_PARENT_TABLE p 
left join MY_CHILD_TABLE c  
    on c.PK = p.PK 
group by p.PK, p.TNAME, p.DEPT;

Chris Saxon

Followup  

May 28, 2020 - 4:16 pm UTC

Cool, good stuff.

May 28, 2020 - 6:30 pm UTC

Reviewer: Don Simpson from Tucson, AZ, USA

Well, I'm an idiot. My tweak works fine for the null condition, but introduces extraneous quote (") characters into the match condition.

Oh well.

Chris Saxon

Followup  

May 29, 2020 - 11:19 am UTC

Doh! I don't see any extra quotes though?

May 29, 2020 - 2:47 pm UTC

Reviewer: Don Simpson from Tucson, AZ, USA

They're wrapping each json object in the array:

{"child":["{"CC":20,"XX":22}","{"CC":40,"XX":44}","{"CC":30,"XX":33}"]}
Chris Saxon

Followup  

May 29, 2020 - 4:13 pm UTC

Yeah, I'm not seeing that. What are you using to run the query? It may be a client formatting issue.

May 29, 2020 - 8:56 pm UTC

Reviewer: Don Simpson from Tucson, AZ, USA

I get the same results with both SqlPlus and Toad.

May 29, 2020 - 8:56 pm UTC

Reviewer: Don Simpson from Tucson, AZ, USA

We're on 12 not 19, if that makes a difference.
Chris Saxon

Followup  

June 03, 2020 - 10:55 am UTC

Running on 12.2 in SQL*Plus I get this:

SQL> select json_object (
  2      'child' value json_arrayagg (
  3          case when c.PK is null
  4              then null
  5              else json_object(
  6                  'CC' value c.CC,
  7                  'XX' value c.XX
  8                  )
  9          end
 10          absent on null
 11        )
 12      ) as J
 13  from MY_PARENT_TABLE p
 14  left join MY_CHILD_TABLE c
 15      on c.PK = p.PK
 16  group by p.PK, p.TNAME, p.DEPT;

J
------------------------------------------------------------------------------------------------------------------------
{"child":[{"CC":20,"XX":22},{"CC":40,"XX":44},{"CC":30,"XX":33}]}
{"child":[]}


I don't know why you're getting the extra characters.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.