Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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"
   },
   { 
      "key": "key2",
      "value": "value2"
   },
   { 
      "key": "key3",
      "value": "value3_NO"
   },
   { 
      "key": "key4no",
      "value": "value4_NO"
   },
   { 
      "key": "key5",
      "value": "value5"
   }
 ]
}')                    -- no match for key4
, (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"
   }
 ]
}')                    -- no match for key3
, (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"
   }
 ]
}');                    -- no match for either key3 or key4


-- best index
CREATE INDEX customer_nodevalue_idx ON customer
USING gin ((customerOrders->'nodeValue') jsonb_path_ops);
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}

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"}]}