PostgreSQL - Get JSONB key/value pair inside object and return it

Question

This operator ?| returns a boolean. It just checks to see if the string is contained in the string.

SELECT ids ?| '{100085,100087,100090,100091,100093,100095,100113,
100121,100126,100211,100213,100223,100324,100326}' 
FROM tableA

|ids|
{'100090':'123456789','100096':'987654321'}

Which will return true because id 100090 is in the ids jsonb column.

Question is, is there a way to return what it finds and not just a boolean. Instead of true, return the value of the match it finds e.g. return '123456789'

Sami Kuhmonen · Accepted Answer · 2019-07-21 05:54:11Z

2

One possibility, a bit complex, but at least functions and returns all values that are found with those keys. If you want just one then needs a limit:

SELECT * FROM 
  (SELECT '{"100090":"123456789","100096":"987654321"}'::jsonb ->>
    unnest('{100085,100087,100090,100091,100093,100095,100113,100121,100126,100211,100213,100223,100324,100326}'::int[])::text AS result) AS x
  WHERE result IS NOT NULL;

Result:

  result
-----------
 123456789
(1 row)

Needs the subselect since for some reason trying to add the IS NOT NULL into the query otherwise just claims the column doesn't exist.

answered Jul 21, 2019 at 5:54

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

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

1 Comment

Panama Jack Over a year ago

Actually never mind that last question, it works how I need it. thanks for this. I've been trying everything. Instead of casting it as text can it be a bigint? I tried but then it comes as empty column.

Collectives™ on Stack Overflow

PostgreSQL - Get JSONB key/value pair inside object and return it

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