0

I havejsonb columnfeeds with data in the form of this:

[{  "id": 99999999,  "lang": "pt",  "entities": {    "urls": [],    "media": [{      "id": 123456456,      "type": "photo",      "id_str": "123456456",      "indices": [37, 59],    }]  },  "favorited": false,  "retweeted": false,  "truncated": false}]

How do I query thefirstmedia ->> id since it's in an json array? So that the result is like this:

   id             media_id---------------------------99999999         123456456

I triedjsonb_array_elements but can't seem to get the output I want, especially the part where themedia is nested within another array.

askedFeb 16, 2017 at 4:02
Andy's user avatar

2 Answers2

2

Figured it out

SELECT  jsonb_array_elements(feeds)->>'id',  (((jsonb_array_elements(feeds)->'entities')->'media')->0)->>'id' as mediaFROM mytable
answeredFeb 16, 2017 at 4:29
Andy's user avatar
Sign up to request clarification or add additional context in comments.

Comments

0
select feeds::jsonb->0->>'entities'->>'media'->0->>'id' from table;
answeredFeb 16, 2017 at 4:29
Rahul Sharma's user avatar

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.