Difficult merging of jsonb object array and columns
<p>I have a table</p>

<p><code>Visitor: (id, signup_up, sessions, email, custom_fields)</code></p>

<p>Where <code>custom_fields</code> is an jsonb array of JSON objects of the form</p>

<p><code>CustomField: ( field, value )</code> example: <code>(domain, www.somedomain.com)</code></p>

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

<p>EXAMPLE</p>

<p>Given these rows</p>

<pre><code>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" }]
</code></pre>

<p>I'd like to get</p>

<pre><code>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"}]
</code></pre>

<p>Any idea on how this can be accomplished?</p>

<p>Any help is much appreciated</p>