Get specific values from a jsonb column

Question

I have a table Customer

• customerId(int)
• customerName(string)
• customerOrders(jsonB)

customerOrders has the below structure:

{ 
 "nodeValue":[
   { 
      "key": "key1",
      "value": "value1"
   },
   { 
      "key": "key2",
      "value": "value2"
   },
   { 
      "key": "key3",
      "value": "value3"
   },
   { 
      "key": "key4",
      "value": "value4"
   },
   { 
      "key": "key5",
      "value": "value5"
   }
 ]
}

I am trying to get the value of nodeValue of the rows key = 'key3' AND key = 'key4'.

For example: return the value of 'key3' and 'key4', WHERE key = 'key3' AND key = 'key4'.

I am trying to do something like:

SELECT value, value 
from public.customers 
where nodeValue.key3 = 'key3' 
  AND nodeValue.key4 = 'key4'

Answer 1

with orders as 
(
   select jsonb_array_elements(customerOrders->'nodeValue') as orders
   from customers
) 
select orders->'value' as val 
from orders 
where 
    orders->>'key' = 'key3' 
or  orders->>'key' = 'key4';

result:

value1

value2

Please note the use of the operator ->> to get the value as text

db-fiddle

Answer 2

get the value of nodeValue of the rows where key = 'key3' AND key = 'key4'.

Best use the jsonb "contains" operator @>:

SELECT customerid, customerOrders->'nodeValue'
FROM   customer
WHERE  customerOrders->'nodeValue' @> '[{"key": "key3"}]'
AND    customerOrders->'nodeValue' @> '[{"key": "key4"}]';

To make it fast for big tables support it with an index. Ideally a jsonb_path_ops index:

CREATE INDEX customer_nodeValue_idx ON customer
USING gin ((customerOrders->'nodeValue') jsonb_path_ops);  -- parentheses required

See:

• What's the proper index for querying structures in arrays in Postgres jsonb?

The later version of your question is a bit more tricky:

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

First filter qualifying rows like above (with a fast index lookup). Then unnest the JSON document and build your answer in a LATERAL subquery. Should be simplest, cleanest and fastest. For the technique, see:

• How to turn JSON array into Postgres array?

I added customerid to the result of both queries to identify rows. That's optional.

db<>fiddle here (demonstrating both)

Design

You might simplify your bloated JSON layout to something like:

'{
    "key1": "value1",
    "key2": "value2",
    "key3": "value3",
    "key4": "value4",
    "key5": "value5"
}'

Or at least:

'[
    {
        "key1": "value1"
    },
    {
        "key2": "value2"
    },
    {
        "key3": "value3"
    },
    {
        "key4": "value4"
    },
    {
        "key5": "value5"
    }
]'

Would make everything considerably simpler, smaller and faster.

Answer 3

I suspect what you actually want is

SELECT
  (SELECT orders->>'value'
    FROM json_array_elements(customerOrders->'nodeValue') AS orders
    WHERE orders->>'key' = 'key3'
  ) AS value_key3,
  (SELECT orders->>'value'
    FROM json_array_elements(customerOrders->'nodeValue') AS orders
    WHERE orders->>'key' = 'key4'
  ) AS value_key4
FROM public.customers;

but really your data structure is not suitable for this purpose. Instead of an array with key-value pairs, use an object that has the keys and values as properties. With that, you could access the result easily with

SELECT
  customerOrders->'nodeValue'->>'key3' AS value_key3,
  customerOrders->'nodeValue'->>'key4' AS value_key4
FROM public.customers;

Answer 4

First you need to create a type (or table)

CREATE TYPE key_value_type AS (
    key text,
    value text
);

Then you can use jsonb_populate_recordset function

SELECT
    r_key1.value AS key1,
    r_key2.value AS key2
FROM
    customers c
    JOIN LATERAL jsonb_populate_recordset(NULL::key_value_type, customerOrders -> 'nodeValue') r_key1 
      ON r_key1.key = 'key1'
    JOIN LATERAL jsonb_populate_recordset(NULL::key_value_type, customerOrders -> 'nodeValue') r_key2 
      ON r_key2.key = 'key2'

Alternatively with jsonb_to_recordset function (without creating a type)

SELECT
    r_key1.value AS key1,
    r_key2.value AS key2
FROM
    customers c
    JOIN LATERAL jsonb_to_recordset(customerOrders -> 'nodeValue') AS r_key1 (key text,
        value text) ON r_key1.key = 'key1'
    JOIN LATERAL jsonb_to_recordset(customerOrders -> 'nodeValue') AS r_key2 (key text,
        value text) ON r_key2.key = 'key2'

Here is the result for both alternatives;

| key1   | key2   |
| ------ | ------ |
| value1 | value2 |