Postgres JSON as TEXT cannot access the needed data

Question

Hello I'm a new Data Engineer so I'm still learning. I just started in a company and I notice that they bring data from an API as JSON but they store it in Postgres as text. I thought that I could convert it to JSON and then try to access the data that I need from the column but I get everything as null values when I run my query, below please find an example of the data and the query. Any help will be appreciated :)

[
  {
    "name": "Vorwurf",
    "value": "Geschwindigkeitsverstoß",
    "type": "DROPDOWN_LIST",
    "isDefault": true,
    "isRequired": false,
    "option": "Geschwindigkeitsverstoß, Rotlichtverstoß, Abstandsverstoß, Handyverstoß, Alkohol, Anderer Tatvorwurf"
  },
  {
    "name": "Schreiben erhalten?",
    "value": "No",
    "type": "CHECKBOX",
    "isDefault": true,
    "isRequired": false
  },
  {
    "name": "Besteht Rechtsschutzversicherung",
    "value": "Yes",
    "type": "CHECKBOX",
    "isDefault": true,
    "isRequired": true
  },
  {
    "name": "Welches Schreiben liegt vor?",
    "value": "Bußgeldbescheid",
    "type": "DROPDOWN_LIST",
    "isDefault": true,
    "isRequired": false,
    "option": "Bußgeldbescheid, Anhörungsbogen, Zeugenfragebogen, Kein Schreiben"
  },
  {
    "name": "Lead Nummer",
    "value": "GM00002153",
    "type": "TEXT",
    "isDefault": true,
    "isRequired": false
  },
  {
    "name": "URL zum vorliegenden Schreiben",
    "value": null,
    "type": "TEXT",
    "isDefault": true,
    "isRequired": false
  },
  {
    "name": "Angabe Website - Wie möchten Sie uns Ihre Unterlagen senden?",
    "type": "DROPDOWN_LIST",
    "isDefault": true,
    "isRequired": false,
    "option": "Fax, E-Mail, Unterlagen bereits hochgeladen"
  }
]

With t as (select cast(custom_fields as json)
from test.matters),
CF as (SELECT custom_fields ->> 'name' as "name",
       custom_fields ->> 'isDefault' as "isDefault", 
       custom_fields ->> 'isRequired' as "isRequired",
       custom_fields ->> 'option' as "option",
       custom_fields ->> 'groups' as "groups",
       custom_fields ->> 'practice_ids' as "practice_ids",
       custom_fields ->> 'value' as "value"
from t)
select * from CF

I also tried doing converting the text as json like this:

SELECT custom_fields::json->>'name' FROM test.matters_che

score 1 · Accepted Answer · 2021-07-13 13:26:13Z

1

Your JSON value is an array, so you need to unnest the array (turn each element into a row). The ->> operator does not work with arrays, only with "regular" JSON objects.

Unnesting an array of JSON objects into rows can be done using jsonb_array_elements(). If you want to use the column expressions in the WHERE clause you need to wrap the query in a derived table (or a common table expression)

select *
from (
  SELECT cf.item ->> 'name' as "name",
         cf.item ->> 'isDefault' as "isDefault", 
         cf.item ->> 'isRequired' as "isRequired",
         cf.item ->> 'option' as "option",
         cf.item ->> 'groups' as "groups",
         cf.item ->> 'practice_ids' as "practice_ids",
         cf.item ->> 'value' as "value"
  from your_table t
    cross join jsonb_array_elements(t.custom_fields::jsonb) as cf(item)
 ) t
 WHERE name = '....'

Online Example

edited Jul 13, 2021 at 13:26

answered Jul 13, 2021 at 11:59

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres JSON as TEXT cannot access the needed data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related