PostgreSQL get any value from jsonb object

Question

I want to get the value of either key 'a' or 'b' if either one exists. If neither exists, I want the value of any key in the map.

Example:

'{"a": "aaa", "b": "bbbb", "c": "cccc"}' should return aaa.
'{"b": "bbbb", "c": "cccc"}' should return bbb.
'{"c": "cccc"}' should return cccc.

Currently I'm doing it like this: SELECT COALESCE(o ->> 'a', o ->> 'b', o->> 'c') FROM...

The problem is that I don't really want to name key 'c' explicitly since there are objects that can have any key.

So how do I achieve the desired effect of "Get value of either 'a' or 'b' if either exists. If neither exists, grab anything that exists."?

I am using postgres 9.6.

Vao Tsun · Accepted Answer · 2018-01-15 08:04:15Z

1

maybe too long:

t=# with c(j) as (values('{"a": "aaa", "b": "bbbb", "c": "cccc"}'::jsonb))
, m as (select j,jsonb_object_keys(j) k from c)
, f as (select * from m where k not in ('a','b') limit 1)
t-# select COALESCE(j ->> 'a', j ->> 'b', j->>k) from f;
 coalesce
----------
 aaa
(1 row)

and with no a,b keys:

t=# with c(j) as (values('{"a1": "aaa", "b1": "bbbb", "c": "cccc"}'::jsonb))
, m as (select j,jsonb_object_keys(j) k from c)
, f as (select * from m where k not in ('a','b') limit 1)
select COALESCE(j ->> 'a', j ->> 'b', j->>k) from f;
 coalesce
----------
 cccc
(1 row)

Idea is to extract all keys with jsonb_object_keys and get the first "random"(because I don't order by anything) (limit 1) and then use it for last coalesce invariant

answered Jan 15, 2018 at 8:04

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

siltalau Over a year ago

Thank you for that. I wasn't able to find any way to do it. For the current use case this is a bit much but now I know there is a way to do it. My first thought was a simple o ->> 0 like you do for arrays but that just wasn't to be...

Collectives™ on Stack Overflow

PostgreSQL get any value from jsonb object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related