CODEWITHSUNDEEP
postgresqljsonb
A. Canyon
A. Canyon

Reputation: 612

Wildcards in JSONB

Looking for a wildcard operator to query JSONB data types. For example with this:

$> Campaign.find(43).style_item_map

{"0"=>{"items"=>[876, 875], "style"=>"First", "featured"=>[876, 875]}, "1"=>{"items"=>[999, 998], "style"=>"Secondary",
"featured"=>[]}}

Looking for a query like this: (% doesn't work here)

Campaign.where("style_item_map @> '{\"%\":{\"items\": [876]}}'")

Upvotes: 0

Views: 1813

Answers (1)

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

I assume you need to use jsonb_each twice for this kind of document:

WITH t(style_item_map) AS ( VALUES
  ('{
  "0": {
    "items":[876, 875],
    "style":"First",
    "featured":[876, 875]
  },
  "1": {
    "items":[999, 998],
    "style":"Secondary",
   "featured":[]
   }
}'::JSONB)
)
SELECT
  item.key,
  inner_item
FROM t,
    jsonb_each(t.style_item_map) item,
    jsonb_each(item.value) inner_item
WHERE
  inner_item.key = 'items'
AND
  inner_item.value @> '[876]';

Result:

 key |      inner_item      
-----+----------------------
 0   | (items,"[876, 875]")
(1 row)

Upvotes: 1

Related Questions