Difficult merging of jsonb object array and columns

Question

I have a table

Visitor: (id, signup_up, sessions, email, custom_fields)

Where custom_fields is an jsonb array of JSON objects of the form

CustomField: ( field, value ) example: (domain, www.somedomain.com)

I want to take the signed_up, sessions, email columns and their values, and the CustomField json objects inside the custom_fields array, and merge them into a 3rd field called data using the same CustomField structure, ie. each entry has the form field: value.

EXAMPLE

Given these rows

id | sessions | email              | custom_fields
---------------------------------------------------------------
1  | 3        | [email protected]      [{ field: domain, value: "www.hello.com" }, { field: type, value: "Customer" }]
---------------------------------------------------------------
2  | 5        | [email protected]   [{ field: domain, value: "www.other.com" }, { field: type, value: "Customer" }]

I'd like to get

id | fields
-----------------------
1  | [{sessions: 3, email: [email protected], domain: "www.hello.com", type: "Customer"}]
----------------------
2  | [{sessions: 5, email: [email protected], domain: "www.other.com", type: "Customer"}]

Any idea on how this can be accomplished?

Any help is much appreciated

klin · Accepted Answer · 2016-05-17 22:21:28Z

7

Example data (this should be a part of the question, not an answer; note the proper json syntax):

create table visitor (id int, sessions int, email text, custom_fields jsonb);
insert into visitor values
(1, 3, '[email protected]', '[{"field": "domain", "value": "www.hello.com" }, {"field": "type", "value": "Customer"}]'),
(2, 5, '[email protected]', '[{"field": "domain", "value": "www.other.com" }, {"field": "type", "value": "Customer"}]');

Tip 1. Use jsonb_array_elements() and select json values of field and value in columns key and value:

select id, sessions, email, elem->>'field' as key, elem->>'value' as value
from visitor, jsonb_array_elements(custom_fields) elem;

 id | sessions |       email       |  key   |     value     
----+----------+-------------------+--------+---------------
  1 |        3 | [email protected]    | domain | www.hello.com
  1 |        3 | [email protected]    | type   | Customer
  2 |        5 | [email protected] | domain | www.other.com
  2 |        5 | [email protected] | type   | Customer
(4 rows)

Tip 2. Use jsonb_object_agg() to aggregate these pairs (key, value) into a json object:

select 
    id, 
    jsonb_object_agg(key, value)
from (
    select id, sessions, email, elem->>'field' as key, elem->>'value' as value
    from visitor, jsonb_array_elements(custom_fields) elem
    ) s
group by id, sessions, email
order by id;

 id |                jsonb_object_agg                 
----+-------------------------------------------------
  1 | {"type": "Customer", "domain": "www.hello.com"}
  2 | {"type": "Customer", "domain": "www.other.com"}
(2 rows)

Final query. Add (concatenate) json objects built from columns session and email, and build a json array with all objects:

select 
    id, 
    json_build_array(
        jsonb_object_agg(key, value) ||
        jsonb_build_object('sessions', sessions, 'email', email)
        ) as fields
from (
    select id, sessions, email, elem->>'field' as key, elem->>'value' as value
    from visitor, jsonb_array_elements(custom_fields) elem
    ) s
group by id, sessions, email
order by id;

 id |                                             fields                                             
----+------------------------------------------------------------------------------------------------
  1 | [{"type": "Customer", "email": "[email protected]", "domain": "www.hello.com", "sessions": 3}]
  2 | [{"type": "Customer", "email": "[email protected]", "domain": "www.other.com", "sessions": 5}]
(2 rows)

One more tip (or a trick):

select '{"a": null}'::jsonb || '{"a": 1}'::jsonb;

 ?column? 
----------
 {"a": 1}
(1 row)

edited May 17, 2016 at 22:21

answered May 17, 2016 at 21:58

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

Tarlen Over a year ago

Wow this is awesome, much appreciated. Say that some visitors had an additional custom field that other's didn't. How would I go about 'nullifying', (ie. save the key, but set it to null) this field of all the visitors who does not have it.

Tarlen Over a year ago

I', not sure how you'd do that for arbitrary keys (here you hardcode 'a', but I wont know the name of the field)

klin Over a year ago

It is not a simple issue. You can find some tips in this post: Postgres: Flatten aggregated key/value pairs from a JSONB field? or ask a new more specific question.

Collectives™ on Stack Overflow

Difficult merging of jsonb object array and columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related