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?
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