Skip to content

Instantly share code, notes, and snippets.

@MrHen

MrHen/json_unwind.sql

Created January 18, 2018 21:27
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save MrHen/7ea65ea401d137c4c732ea960d31e642 to your computer and use it in GitHub Desktop.
Save MrHen/7ea65ea401d137c4c732ea960d31e642 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres JSON Unwind
Raw
json_unwind.sql
with contacts as (
select cr.id as contact_record_id
, cr.customer_id
, cr.form_version_id
, r.key as question_id
, r.value#>>'{}' as question_response
from cx_snapshot.contacts_newcontactrecord cr
, jsonb_each(cr.fields::jsonb->'responses') r
)
, questions as (
select f.id as form_version_id
, f.form_id as form_id
, c.value::jsonb->>'uuid' as question_id
, c.value::jsonb->>'question' as question_text
, c.value::jsonb->>'position' as question_position
from cx_snapshot.forms_formversion f
, jsonb_array_elements(f.contents::jsonb) c
)
select c.contact_record_id
, c.customer_id
, q.form_id
, c.form_version_id
, c.question_id
, q.question_text
, c.question_response
from contacts c
join questions q on (q.form_version_id = c.form_version_id and q.question_id = c.question_id)
order by c.customer_id, c.contact_record_id, q.question_position
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment