By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE customer (
customerid int
, customername text
, customerorders jsonb
);
INSERT INTO customer VALUES
(1, 'foo', '{
"nodeValue":[
{
"key": "key1",
"value": "value1"
},
{
"key": "key2",
"value": "value2"
},
{
"key": "key3",
"value": "value3_YES"
},
{
"key": "key4",
"value": "value4_YES"
},
{
"key": "key5",
"value": "value5"
}
]
}') -- the only match
, (2, 'bar', '{
"nodeValue":[
{
"key": "key1",
"value": "value1"
4 rows affected
TABLE customer;
| customerid | customername | customerorders |
|---|---|---|
| 1 | foo | {"nodeValue": [{"key": "key1", "value": "value1"}, {"key": "key2", "value": "value2"}, {"key": "key3", "value": "value3_YES"}, {"key": "key4", "value": "value4_YES"}, {"key": "key5", "value": "value5"}]} |
| 2 | bar | {"nodeValue": [{"key": "key1", "value": "value1"}, {"key": "key2", "value": "value2"}, {"key": "key3", "value": "value3_NO"}, {"key": "key4no", "value": "value4_NO"}, {"key": "key5", "value": "value5"}]} |
| 3 | baz | {"nodeValue": [{"key": "key1", "value": "value1"}, {"key": "key2", "value": "value2"}, {"key": "key3no", "value": "value3_NO"}, {"key": "key4", "value": "value4_NO"}, {"key": "key5", "value": "value5"}]} |
| 4 | bam | {"nodeValue": [{"key": "key1", "value": "value1"}, {"key": "key2", "value": "value2"}, {"key": "key3no", "value": "value3_NO"}, {"key": "key4no", "value": "value4_NO"}, {"key": "key5", "value": "value5"}]} |
-- get the value of nodeValue of the rows where key = 'key3' AND key = 'key4'
SELECT customerid, customerOrders->'nodeValue'
FROM customer
WHERE customerOrders->'nodeValue' @> '[{"key": "key3"}]'
AND customerOrders->'nodeValue' @> '[{"key": "key4"}]';
| customerid | ?column? |
|---|---|
| 1 | [{"key": "key1", "value": "value1"}, {"key": "key2", "value": "value2"}, {"key": "key3", "value": "value3_YES"}, {"key": "key4", "value": "value4_YES"}, {"key": "key5", "value": "value5"}] |
-- return the value of key3 AND key4
SELECT c. customerid, o.values
FROM customer c
CROSS JOIN LATERAL (
SELECT ARRAY(
SELECT o.ord->>'value'
FROM jsonb_array_elements(c.customerOrders->'nodeValue') o(ord)
WHERE (o.ord->>'key' = 'key3' OR
o.ord->>'key' = 'key4')
)
) o(values)
WHERE c.customerOrders->'nodeValue' @> '[{"key": "key3"}]'
AND c.customerOrders->'nodeValue' @> '[{"key": "key4"}]';
| customerid | values |
|---|---|
| 1 | {value3_YES,value4_YES} |