Postgres extract value from jsonb array

Question

I have a jsonb field with an array like this one below:

[  
   {  
      "type":"discount",
      "title":"Discount 10%"
   },
   {        
      "file":"zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf",
      "type":"menu",
      "title":"Some menu title etc"
   }
]

I want to get the file attribute in case there is a type=menu in the array.

What I managed to do is to know if there is one, but how can I eventually extract the file value?

case when offers @> '[{"type":"menu"}]' then true else false end

I don't want to do something like this below because the array may not contain a discount type.

offers->1->'file'

try json_array_elements for this?..

Vao Tsun
– Vao Tsun

2017-02-09 11:55:57 +00:00
Commented Feb 9, 2017 at 11:55 — Vao Tsun
– Vao Tsun, Commented Feb 9, 2017 at 11:55
@VaoTsun no luck there

mallix
– mallix

2017-02-09 12:03:41 +00:00
Commented Feb 9, 2017 at 12:03 — mallix
– mallix, Commented Feb 9, 2017 at 12:03

klin · Accepted Answer · 2017-02-09 12:05:58Z

5

Use jsob_array_elements() and ->> operator (see JSON Functions and Operators.)

with a_table(json_col) as (
values (
'[  
   {  
      "type":"discount",
      "title":"Discount 10%"
   },
   {        
      "file":"zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf",
      "type":"menu",
      "title":"Some menu title etc"
   }
]'::jsonb)
)

select value->>'file' as filename
from a_table,
lateral jsonb_array_elements(json_col)
where value->>'type' = 'menu'

                                    filename                                     
---------------------------------------------------------------------------------
 zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf
(1 row)

answered Feb 9, 2017 at 12:05

klin

123k15 gold badges239 silver badges262 bronze badges

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

2 Comments

mallix Over a year ago

why do you use lateral instead of plain jsonb_array_elements ?

klin Over a year ago

Functions that return set of rows should be called in the FROM clause and lateral join is a natural way to do that.

Vao Tsun · Accepted Answer · 2017-02-09 12:07:24Z

1

Eg:

t=# with a as (with v as (select '[
   {
      "type":"discount",
      "title":"Discount 10%"
   },
   {
      "file":"zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf",
      "type":"menu",
      "title":"Some menu title etc"
   }
]'::jsonb j)
select jsonb_array_elements(j) r from v) select r->>'file' from a where r->>'type' = 'menu';
                                    ?column?
---------------------------------------------------------------------------------
 zx5rP8EoacyfhqGndcSOnP8VYtkr9Ya8Nvf7oYL98YDsM1CLMYIurYvfVUU4AGkzBsovwssT0bq.pdf
(1 row)

answered Feb 9, 2017 at 12:07

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Postgres extract value from jsonb array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related