CODEWITHSUNDEEP
postgresql
Fomalhaut
Fomalhaut

Reputation: 9727

How do I extract a value of a JSON with arbitrary key in PostgreSQL?

I have a field with type jsonb that contains objects with keys and their values. Examples:

{"a": "qwe"}
{"b": "rty"}
{"a": "asd", "b": "123"}

I need an expression that returns a value in the JSON independently on its key. If there are several keys, I need any of them (first one, for example). The desirable result for the values above is:

"qwe"
"rty"
"asd"  -- or "123"

Upvotes: 0

Views: 79

Answers (1)

user330315
user330315

Reputation:

You can use jsonb_each_text() for that:

select j.val
from the_table t
  cross join lateral jsonb_each_text(t.the_json_column) as j(k,val);

That will return both values, asd and 123 from the third row

Online example

An improvement to have one arbitrary value:

select j.val
from the_table t
cross join lateral (
    select * from jsonb_each_text(t.the_json_column) as j(k,val)
    limit 1
) j

Upvotes: 1

Related Questions