Selectively pluck keys and values based on array from JSONB column

Question

I'm looking for a way to pluck values from a JSONB object using an array of keys. Here's my JSON:

{
  "Foo1": 1,
  "Foo2": 2,
  "Foo3": 3,
  "Foo3": 4
}

I have a variable called "@Fields" which is of type TEXT[]. The array contains the name of the keys I'd like to pluck from the object ie. {'Foo1', 'Foo2'}. The result should be:

{
  "Foo1": 1,
  "Foo2": 2
}

I was using JSONB_EXTRACT_PATH("Data"::jsonb, "@Fields") however it seems the function requires passing in the paths as individual parameters whereas I want to give it an array somehow. Here's how it looks in my query:

SELECT
  "UserID",
  (
    CASE
      WHEN ARRAY_LENGTH("@Fields", 1) = 0 THEN "Data"
      ELSE JSONB_EXTRACT_PATH("Data", "@Fields")
    END
  ) AS "Data"
FROM
    UserMeta

I suspect I'll have to use JSON_EACH or something similar?

pozs · Accepted Answer · 2017-04-20 15:34:12Z

2

You can only remove keys one-by-one with the - operator. For everything else, you'll need a sub-select, where you extract each key-value pair, filter them (here comes your logic; which can be anything BTW), then aggregate the values together:

(select jsonb_object_agg(key, value)
 from   jsonb_each(data)
 where  key = any(keys_should_stay)) sub_select

Example use in context: http://rextester.com/OANQ93761

EDIT: If you want specific meaning to an empty array (i.e. retain all of the keys), use this predicate instead:

where  key = any(keys_should_stay)
or     cardinality(keys_should_stay) = 0

answered Apr 20, 2017 at 15:34

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

Selectively pluck keys and values based on array from JSONB column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related