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        | test@gmail.com      [{ field: domain, value: "www.hello.com" }, { field: type, value: "Customer" }]
---------------------------------------------------------------
2  | 5        | another@gmail.com   [{ field: domain, value: "www.other.com" }, { field: type, value: "Customer" }]

I'd like to get

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

Any idea on how this can be accomplished?

Any help is much appreciated

klin · Accepted Answer · 2016-05-17T22:21:28.290

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, 'test@gmail.com', '[{"field": "domain", "value": "www.hello.com" }, {"field": "type", "value": "Customer"}]'),
(2, 5, 'another@gmail.com', '[{"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 | test@gmail.com    | domain | www.hello.com
  1 |        3 | test@gmail.com    | type   | Customer
  2 |        5 | another@gmail.com | domain | www.other.com
  2 |        5 | another@gmail.com | 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": "test@gmail.com", "domain": "www.hello.com", "sessions": 3}]
  2 | [{"type": "Customer", "email": "another@gmail.com", "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)

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, May 17 '16 at 22:04
I', not sure how you'd do that for arbitrary keys (here you hardcode 'a', but I wont know the name of the field) — Tarlen, May 18 '16 at 06:38
It is not a simple issue. You can find some tips in this post: [Postgres: Flatten aggregated key/value pairs from a JSONB field?](http://stackoverflow.com/a/35179515/1995738) or ask a new more specific question. — klin, May 18 '16 at 07:41

Difficult merging of jsonb object array and columns

1 Answers1