Join the Stack Overflow Community
Stack Overflow is a community of 6.8 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite
1

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

share|improve this question
1  
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. – a_horse_with_no_name Jan 19 at 15:22
    
please have a look at json.org – Vao Tsun Jan 19 at 15:25
    
col->'action_information_1'->0->>'value' , if you need array index parametrized jsonb_extract_path(col->'action_information_1',?)->>'value' – cske Jan 19 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 Jan 19 at 19:18
    
And this seems to be the big barrier to using in it's original form. – Dmac Jan 19 at 19:20
up vote 2 down vote

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.