PostgreSQL difficulty extracting field from jsonb column

Question

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

Your example is an invalid JSON document. You need to use " around keys and values not ' and the array [...] must not be enclosed in double quotes. — user330315
– user330315, Commented Jan 19, 2017 at 15:22
col->'action_information_1'->0->>'value' , if you need array index parametrized jsonb_extract_path(col->'action_information_1',?)->>'value' — cske
– cske, Commented Jan 19, 2017 at 15:27
it's interesting, when I run the original text through a JSON validator, it comes back 'valid'. { "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'}]" } — Dmac
– Dmac, Commented Jan 19, 2017 at 19:18
And this seems to be the big barrier to using in it's original form. — Dmac
– Dmac, Commented Jan 19, 2017 at 19:20

score 2 · Accepted Answer · 2017-01-19 15:34:45Z

2

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

Collectives™ on Stack Overflow

PostgreSQL difficulty extracting field from jsonb column

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