Reconstruct JSONB Nested Arrays

Question

I have the following JSONB column called value.

create temp table settings as
select 
   '{"favorites": [
      {
      "listings": [
       {"id": "aa92f346-7a93-4443-949b-4eab0badd983", "version": 1},
       {"id": "cd92e346-6b04-3456-050a-5eeb0bddd027", "version": 3},
       {"id": "cd92e346-6b04-3456-050a-5eeb0bddd333", "version": 2}
       ]
      }
]}'::jsonb as value;

So I'm trying to remove items from this nested array and reconstruct it like so

select jsonb_set(value,'{favorites}',jsonb_set('{}','{listings}', 
       jsonb_agg(new_elems))   ) as ne  from settings s ,
lateral ( select  jsonb_array_elements(elem->'listings')as new_elems 
       from jsonb_array_elements(value->'favorites')elem) sets where  
       sets.new_elems->>'id' != 'aa92f346-7a93-4443-949b-4eab0badd983' 
       group by s.value

Which works but the "favorites" array is flattened.

Instead of

"{"favorites": [{"listings": [{"id": "cd92e346-6b04-3456-050a-5eeb0bddd027", "version": 3}, {"id": "cd92e346-6b04-3456-050a-5eeb0bddd333", "version": 2}]}]}"

I get

"{"favorites": {"listings": [{"id": "cd92e346-6b04-3456-050a-5eeb0bddd027", "version": 3}, {"id": "cd92e346-6b04-3456-050a-5eeb0bddd333", "version": 2}]}}"

Does anyone have an idea on how can I solve this?.

Haleemur Ali · Accepted Answer · 2018-02-17 19:04:06Z

1

you can create jsonb arrays by using the functions JSONB_AGG or JSON_BUILD_ARRAY. JSONB_AGG is an aggregate function, whereas JSONB_BUILD_ARRAY puts all provided arguments in an array.

Below, I used JSONB_BUILD_ARRAY to make favorites into an json array rather than a key-value

SELECT 
  JSONB_SET(
    '{}', '{favorites}', 
    JSONB_BUILD_ARRAY(
      JSONB_SET('{}', '{listings}', JSONB_AGG(listings))
    )
  )
FROM settings,
LATERAL (SELECT 
           JSONB_ARRAY_ELEMENTS(JSONB_ARRAY_ELEMENTS(value->'favorites')->'listings') listings
         FROM settings) listings
WHERE listings.listings->>'id' != 'aa92f346-7a93-4443-949b-4eab0badd983'

answered Feb 17, 2018 at 19:04

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

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

Collectives™ on Stack Overflow

Reconstruct JSONB Nested Arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related