Tarlen Tarlen - 6 months ago 24
SQL Question

Difficult merging of jsonb object array and columns

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

Answer

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)