Get value from JSON array in postgres-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.