Get value from JSON array in postgres

Question

I have a strange JSON array in my Postgres Database without curly brackets. It has the datatype Text so i need to cast it in a JSON i guess.

It looks like this and can change from row to row.

    [
  [
    "-",
    "name",
    "Gates"
  ],
  [
    "-",
    "name_1",
    null
  ],
  [
    "-",
    "name_2",
    null
  ],
  [
    "-",
    "na_cd",
    null
  ],
  [
    "-",
    "class_cd",
    null
  ],
  [
    "-",
    "reference",
    "190955"
  ],
  [
    "-",
    "lang_cd",
    "en"
  ],
  [
    "-",
    "uid_nr",
    null
  ],
  [
    "-",
    "id",
    19000
  ],
  [
    "-",
    "firstname",
    "Bill"
  ],
  [
    "-",
    "spare",
    null
  ]
]

What i need is to find and print the ID if there is one. In this example 19000.

Can someone please help how can do this?

What if there is more than one array element with id ?

user330315
– user330315

2021-09-28 15:26:50 +00:00
Commented Sep 28, 2021 at 15:26 — user330315
– user330315, Commented Sep 28, 2021 at 15:26

klin · Accepted Answer · 2021-09-28 15:48:46Z

2

Basically, you should use jsonb_array_elements() twice, for the main array and for its filtered element (which is an array too).

select value::numeric as result
from (
    select elem
    from the_data
    cross join jsonb_array_elements(col) as main(elem)
    where elem ? 'id'
    ) s
cross join jsonb_array_elements(elem)
where jsonb_typeof(value) = 'number'

Try it in Db<>Fiddle.

However, if you want to get exactly the third value from the nested array, the query may be simpler (note that array elements are indexing from 0):

select (elem->2)::numeric as result
from the_data
cross join jsonb_array_elements(col) as main(elem)
where elem ? 'id'

Db<>Fiddle.

edited Sep 28, 2021 at 15:48

answered Sep 28, 2021 at 15:23

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

score 1 · Accepted Answer · 2021-09-29 15:27:52Z

1

If you are using Postgres 12 or later and if the value you are after is always at the third array element, you can use a SQL/JSON path expression:

select jsonb_path_query_first(the_column, '$ ? (@[*] == "id")[2]')::int
from the_table

This assumes that the column is defined as jsonb (which it should be). If it's not, you need to cast it: the_column::jsonb

edited Sep 29, 2021 at 15:27

answered Sep 28, 2021 at 15:45

user330315

3 Comments

user2210516 Over a year ago

ERROR: function jsonb_path_query_first(jsonb, unknown) does not exist :(

user330315 Over a year ago

@user2210516: I assume you are using a current Postgres version. Which version are you using?

user2210516 Over a year ago

PostgreSQL 10.18

Collectives™ on Stack Overflow

Get value from JSON array in postgres

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related