Postgresql jsonb set-union of lists

Question

I am hoping it is straightforward to do the following: Given rows containing jsonb of the form

{
  'a':"hello",
  'b':['jim','bob','kate']
}

I would like to be able to get all the 'b' fields from a table (as in select jsondata->'b' from mytable) and then form a list consisting of all strings which occur in at least one 'b' field. (Basically a set-union.)

How can I do this? Or am I better off using a python script to extract the 'b' entries, do the set-union there, and then store it back into the database somewhere else?

Marlon Abeykoon · Accepted Answer · 2016-01-10 04:40:11Z

1

This gives you the union set of elements in list 'b' of the json.

SELECT array_agg(a order by a) 
FROM (SELECT DISTINCT unnest(txt_arr) AS a FROM
     (SELECT  ARRAY(SELECT trim(elem::text, '"')
                    FROM   jsonb_array_elements(jsondata->'b') elem) AS txt_arr
      FROM   jtest1)y)z;

Query Explanation:

Gets the list from b as jsondata->'b'
Expands a JSON array to a set of JSON values from jsonb_array_elements() function.
Trims the " part in the elements from trim() function.
Converts to an array again using array() function after trimming.
Get the distinct value by unnesting it using unnest() function.
Finally array_agg() is used to form the expected result.

edited Jan 10, 2016 at 4:40

answered Jan 10, 2016 at 4:15

Marlon Abeykoon

12.6k4 gold badges60 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Postgresql jsonb set-union of lists

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