How to add a default value to json_build_object postgresql

Question

My json looks like this:-

{"DestinationLists": [{"name": "TVNZ, Mediaworks, Choice", "destinations": []}, { "name": "TVNZ, Discovery", "destinations": [165, 183, 4155]}]}

My desired output is one row for each object in the array like:-

{"name" : "TVNZ, Mediaworks, Choice", "destinations" : []}
{"name" : "TVNZ, Discovery", "destinations" : [{"Id" : 165}, {"Id" : 183}, {"Id" : 4155}]}

My query is this:-

WITH ExpandedData AS (
    SELECT
        "Id",
        setting,
        json_build_object(
            'name',
             dl->>'name',
             'destinations',
             json_agg(
               json_build_object('Id',dld::text::int)
             )
        ) as DestinationListItem
    FROM
        feature.settings, 
        jsonb_array_elements(value->'DestinationLists') dl,
        jsonb_array_elements(dl->'destinations') dld
    GROUP BY
        "Id",dl->>'name'
)
select * from ExpandedData

But this query is missing destinations where the array is empty so the result I am getting from the query is:-

{"name" : "TVNZ, Discovery", "destinations" : [{"Id" : 165}, {"Id" : 183}, {"Id" : 4155}]}

just one row instead of two. How do I update this query to get the desired outcome?

DBFiddle here:- https://www.db-fiddle.com/f/tDA7SajDoWhXrQaAa1j2qN/0

EDIT: I have been able to further drill down my problem, so the issue is basically happening from this line of the query:-

jsonb_array_elements(dl->'destinations') dld

Basically when the destinations array is empty then jsonb_array_elements does not return anything so I guess the right solution here would be to somehow return an empty array from jsonb_array_elements when an empty array is passed to it.

Can you prepare a DBfiddle ?

IVO GELOV
– IVO GELOV

2021-08-16 15:47:24 +00:00
Commented Aug 16, 2021 at 15:47 — IVO GELOV
– IVO GELOV, Commented Aug 16, 2021 at 15:47
Hi, updated my question with DB fiddle @IVOGELOV

Fakhar Ahmad Rasul
– Fakhar Ahmad Rasul

2021-08-16 16:00:15 +00:00
Commented Aug 16, 2021 at 16:00 — Fakhar Ahmad Rasul
– Fakhar Ahmad Rasul, Commented Aug 16, 2021 at 16:00
Does this answer answer your question?

ggordon
– ggordon

2021-08-17 00:04:46 +00:00
Commented Aug 17, 2021 at 0:04 — ggordon
– ggordon, Commented Aug 17, 2021 at 0:04

Ajax1234 · Accepted Answer · 2021-08-16 16:28:59Z

1

You can use a recursive cte:

with recursive cte(js, ind, r) as (
   select js, 1, case when json_array_length(js -> 'DestinationLists' -> 0 -> 'destinations') = 0 then (js -> 'DestinationLists' -> 0)::jsonb else jsonb_set(js::jsonb, concat('{DestinationLists,', 0, ',destinations}')::text[], (select array_to_json(array_agg(json_build_object('Id', value))) from json_array_elements(js -> 'DestinationLists' -> 0 -> 'destinations'))::jsonb) end from data
   union all
   select js, ind+1, case when json_array_length(js -> 'DestinationLists' -> ind -> 'destinations') = 0 then (js -> 'DestinationLists' -> ind)::jsonb else (jsonb_set(js::jsonb, concat('{DestinationLists,', ind, ',destinations}')::text[], (select array_to_json(array_agg(json_build_object('Id', value))) from json_array_elements(js -> 'DestinationLists' -> ind -> 'destinations'))::jsonb) -> 'DestinationLists' -> ind)::jsonb end from cte where ind < json_array_length(js -> 'DestinationLists')
)
select r from cte;

Output:

r
{"name": "TVNZ, Mediaworks, Choice", "destinations": []}
{"name": "TVNZ, Discovery", "destinations": [{"Id": 165}, {"Id": 183}, {"Id": 4155}]}

answered Aug 16, 2021 at 16:28

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

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

4 Comments

Fakhar Ahmad Rasul Over a year ago

This bit is a little too complicated for me, I am looking into postgresql for about the first time. Where do I fit the recursive part with my existing query?

Ajax1234 Over a year ago

@FakharAhmadRasul In essence, your ExpandedData cte definition can be replaced with cte from my answer above, and select * from ExpandedData can be switched for select r from cte

Ajax1234 Over a year ago

@FakharAhmadRasul Additionally, based on your fiddle, the column name js can be replaced with dest

Fakhar Ahmad Rasul Over a year ago

I have updated my question, do you think trying to return an empty array from jsonb_array_elements incase the input to it is also an empty array would be a better approach? and is it possible?

Collectives™ on Stack Overflow

How to add a default value to json_build_object postgresql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related