Skip to content

Instantly share code, notes, and snippets.

@yalisassoon

yalisassoon/neotree-explode-json.sql

Last active March 11, 2020 21:35
Show Gist options
  • Save yalisassoon/29f1c521af299083654cd38bc50bb174 to your computer and use it in GitHub Desktop.
Save yalisassoon/29f1c521af299083654cd38bc50bb174 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
neotree-explode-json.sql
-- first let's extract the "entries" array
select admission_data->>'entries' from joint_records limit 1;
-- Following query attempts to explode JSON based on the position of objects in the entries array
-- Note that the results show you cannot depend on the position because some entries have more json objects than others
-- Therefore we're gonig to have to find a way to identify the object in the array with the key e.g. `BabyCryTriage` and then return the corresponding value
select
admission_data->'entries'->0->'key' as "is_BabyCryTriage?",
admission_data->'entries'->0->'values'->0->'label' as "BabyCryTriage",
admission_data->'entries'->1->'key' as "is_DangerSigns",
admission_data->'entries'->1->'values'->0->'value' as "DangerSigns",
admission_data->'entries'->2->'key' as "is_DangerSigns2",
admission_data->'entries'->2->'values'->0->'value' as "DangerSigns2",
admission_data->'entries'->3->'key' as "is_RR",
admission_data->'entries'->3->'values'->0->'value' as "RR"
from joint_records limit 100;
-- Let's try transforming the JSON to make the keys easily accessible.
-- We can do this by iterating through the JSON one entry at a time until we get to the end
-- Then we'll have a new JSON with clearly identifiable keys that will enable us to address each element of the JSON easily
-- Currently we can't do this because each element has the same 'key' key.
-- The following does the extraction but I need to update the query to actually build a JSON out of the results, with the correct keys:
create materialized view admissions_form_jsons as
select
uid,
-- we can only create a JSON with 50 entries using json_build_object so doing it over 3 queries and then pulling them together
json_build_object(
--coalexce function ensures if an entry is missing (happens when we get to the end of an array) we just insert {"blank":"blank"} as the next element in the JSON
coalesce( admission_data->'entries'-> 0 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 0 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 1 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 1 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 2 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 2 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 3 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 3 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 4 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 4 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 5 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 5 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 6 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 6 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 7 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 7 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 8 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 8 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 9 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 9 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 10 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 10 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 11 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 11 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 12 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 12 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 13 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 13 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 14 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 14 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 15 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 15 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 16 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 16 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 17 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 17 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 18 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 18 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 19 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 19 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 20 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 20 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 21 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 21 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 22 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 22 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 23 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 23 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 24 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 24 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 25 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 25 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 26 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 26 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 27 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 27 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 28 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 28 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 29 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 29 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 30 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 30 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 31 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 31 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 32 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 32 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 33 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 33 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 34 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 34 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 35 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 35 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 36 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 36 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 37 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 37 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 38 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 38 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 39 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 39 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 40 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 40 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 41 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 41 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 42 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 42 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 43 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 43 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 44 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 44 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 45 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 45 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 46 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 46 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 47 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 47 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 48 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 48 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 49 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 49 ->'values'->0->>'value', 'blank')
) as admission_form_1,
json_build_object(
coalesce( admission_data->'entries'-> 50 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 50 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 51 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 51 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 52 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 52 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 53 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 53 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 54 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 54 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 55 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 55 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 56 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 56 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 57 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 57 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 58 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 58 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 59 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 59 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 60 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 60 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 61 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 61 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 62 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 62 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 63 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 63 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 64 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 64 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 65 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 65 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 66 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 66 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 67 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 67 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 68 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 68 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 69 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 69 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 70 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 70 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 71 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 71 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 72 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 72 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 73 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 73 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 74 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 74 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 75 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 75 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 76 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 76 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 77 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 77 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 78 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 78 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 79 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 79 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 80 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 80 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 81 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 81 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 82 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 82 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 83 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 83 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 84 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 84 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 85 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 85 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 86 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 86 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 87 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 87 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 88 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 88 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 89 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 89 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 90 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 90 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 91 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 91 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 92 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 92 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 93 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 93 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 94 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 94 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 95 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 95 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 96 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 96 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 97 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 97 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 98 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 98 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 99 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 99 ->'values'->0->>'value', 'blank')
) as admission_form_2,
json_build_object(
coalesce( admission_data->'entries'-> 100 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 100 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 101 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 101 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 102 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 102 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 103 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 103 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 104 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 104 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 105 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 105 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 106 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 106 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 107 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 107 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 108 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 108 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 109 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 109 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 110 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 110 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 111 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 111 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 112 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 112 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 113 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 113 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 114 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 114 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 115 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 115 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 116 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 116 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 117 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 117 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 118 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 118 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 119 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 119 ->'values'->0->>'value', 'blank'),
coalesce( admission_data->'entries'-> 120 ->>'key', 'blank'), coalesce( admission_data->'entries'-> 120 ->'values'->0->>'value', 'blank')
) as admission_form_3
from joint_records;
-- We can use a query like the following to build our exploded table:
select
admission_form_1->>'BabyCryTriage'::boolean as BabyCryTriage,
admission_form_1->>'DangerSigns' as DangerSigns,
DangerSigns2
from admissions_form_jsons
limit 100;
-- And a query like the following to identify all the keys we need to unpick:
"select
distinct(json_object_keys(admission_form_1))
from admissions_form_jsons;"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment