Jsonb column transformation in postgres or using dbt

Question

I am trying to see how to flatten the jsonb column titled "additionalFields" below. I can query the table with

SELECT "id","additionalFields" AS "additionalFields" 
FROM "tasks" 
WHERE "additionalFields" @> '[{"name":"Open Cases"}]'

However I want the table "tasks" to get normalized after the source data has been ingested and all the keys in the "additionalFields" column are their own column and contain the values associated with them. I have heard 'dbt' is something to use here but I have no clue how to even start with it.

Any help will be appreciated!

Do you know the names of the columns that you want to get back? — Bergi, Mar 05 '23 at 23:32
@Bergi yes! Here are the columns: ['Total Cases', 'Open Cases', 'Prod Cases', 'Sev1 Cases', 'Sev2 Cases',' Sev1/2 Cases', 'Preview Cases'] — Fariha Baloch, Mar 06 '23 at 16:05

Bergi · Answer 1 · 2023-03-06T18:11:31.420

Option A is to use a lot of subqueries or json path queries:

SELECT
  t."id",
  jsonb_path_query_first(t."additionalFields", 'strict $[*] ?(@.name == "Total Cases") .value') AS "Total Cases",
  jsonb_path_query_first(t."additionalFields", 'strict $[*] ?(@.name == "Open Cases") .value') AS "Open Cases",
  jsonb_path_query_first(t."additionalFields", 'strict $[*] ?(@.name == "Prod Cases") .value') AS "Prod Cases",
  jsonb_path_query_first(t."additionalFields", 'strict $[*] ?(@.name == "Sev1 Cases") .value') AS "Sev1 Cases",
  jsonb_path_query_first(t."additionalFields", 'strict $[*] ?(@.name == "Sev2 Cases") .value') AS "Sev2 Cases",
  jsonb_path_query_first(t."additionalFields", 'strict $[*] ?(@.name == " Sev1/2 Cases") .value') AS " Sev1/2 Cases",
  jsonb_path_query_first(t."additionalFields", 'strict $[*] ?(@.name == "Preview Cases") .value') AS "Preview Cases"
FROM "tasks" t
WHERE t."additionalFields" @> '[{"name":"Open Cases"}]'

To get the result columns not a jsonb but as other types, see How to convert a json value to text, to int, to float and to boolean respectively.

Option B is to use a subquery to transform that array of key-value pairs into a JSON object (much more useful also for storage), then use jsonb_to_record with the required record columns:

SELECT t."id", fields.*
FROM "tasks" t,
LATERAL jsonb_to_record(
  SELECT jsonb_object_agg(value->>'name', value->'value')
  FROM jsonb_array_elements(t."additionalFields")
) AS fields("Total Cases" jsonb, "Open Cases" jsonb, "Prod Cases" jsonb, "Sev1 Cases" jsonb, "Sev2 Cases" jsonb, " Sev1/2 Cases" jsonb, "Preview Cases" jsonb)
WHERE t."additionalFields" @> '[{"name":"Open Cases"}]';

To get the result columns not a jsonb, simply declare them as the respective desired primitive type.

Jsonb column transformation in postgres or using dbt

1 Answers1