Get an array element from an array in a json attribute of a jsonb column

Question

I am using postgres database and I have an array inside a json attribute in a jsonb column and I need to get the first element of that array. The jsonb_column structure is like the below:

"IndustryCode": {
"Code": "111110",
"Keys": [
  "11",
  "111",
  "111110"
],}

So far I can get all the query elements by issuing the query below

select jsonb_array_elements(jsonb_column->'IndustryCode'->'Keys' ) from myindustry;

How can I query to get the first element?

user330315 · Accepted Answer · 2020-11-26 15:25:51Z

1

If you just want the first element, there is no need to unnest the whole array. the -> operator also accepts an integer which specifies the array index.

select jsonb_column -> 'IndustryCode' -> 'Keys' ->> 0 
from myindustry;

Note I used ->> to return the value as text not as jsonb

answered Nov 26, 2020 at 15:25

user330315

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

1 Comment

Charis Soteriades Over a year ago

Thank you so much for you help @a_horse_with_no_name

Collectives™ on Stack Overflow

Get an array element from an array in a json attribute of a jsonb column

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related