CODEWITHSUNDEEP
arraysjsonpostgresqljsonb
Dmac
Dmac

Reputation: 11

PostgreSQL difficulty extracting field from jsonb column

Using PostgreSQL 9.5.5 Given the below example jsonb data in a column:

{
 "item_id": "123456",
 "action_information_1": [ {"value": "259", "action_type": "read"} ],
 "action_information_2": [ {"value": "93",  "action_type": "read"} ],
 "action_information_3": [ {"value": "53",  "action_type": "read"} ],
 "action_information_4": [ {"value": "35",  "action_type": "read"} ]
}

I'm having difficulty programmatically extracting the 'value' from 'action_information_1' which would be 259.

It seems the syntax is slightly different from other examples I've seen, the above has preceding ' " ' in front of the ' [ '.

Any help is appreciated, thank you

Upvotes: 1

Views: 129

Answers (1)

user330315
user330315

Reputation:

If you fix the syntax errors in the JSON document the following works:

with test_data (doc) as (
  values (
   '{
     "item_id": "123456",
     "action_information_1": [{"value": "259", "action_type": "read"}],
     "action_information_2": [{"value": "93", "action_type": "read"}],
     "action_information_3": [{"value": "53", "action_type": "read"}],
     "action_information_4": [{"value": "35", "action_type": "read"}]
    }'::json
  )
)
select doc -> 'action_information_1' -> 0 ->> 'value'
from test_data

doc -> 'action_information_1' gets the array for that key, the -> 0 returns the first array element and ->> 'value' then gets the value that is associated with the key value

Alternatively this can be written a bit shorter using: 

select doc #> '{action_information_1,0}' ->> 'value'
from test_data

Upvotes: 2

Related Questions