Flattening a nested json[b] array in Postgres

Question

I have jsonb column feeds 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 the first media ->> id since it's in an json array? So that the result is like this:

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

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

Andy · Accepted Answer · 2017-02-16 06:53:00Z

2

Figured it out

SELECT
  jsonb_array_elements(feeds)->>'id',
  (((jsonb_array_elements(feeds)->'entities')->'media')->0)->>'id' as media
FROM mytable

edited Feb 16, 2017 at 6:53

answered Feb 16, 2017 at 4:29

Andy

5,4542 gold badges45 silver badges46 bronze badges

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

Comments

Rahul Sharma · Accepted Answer · 2017-02-16 04:29:19Z

0

select feeds::jsonb->0->>'entities'->>'media'->0->>'id' from table;

answered Feb 16, 2017 at 4:29

Rahul Sharma

6,0555 gold badges32 silver badges54 bronze badges

Collectives™ on Stack Overflow

Flattening a nested json[b] array in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related