CODEWITHSUNDEEP
sqljsonpostgresql
falidoro
falidoro

Reputation: 399

Postgres Check for JSON keys

I'm currently working with a postgres database, but my knowledge is limited to SQL. I am working with an orders database and need to select all customers who have purchased a selection of products. My table looks as follows:

order_id      customer_name   products_ordered
1             a               {"apples":3, "pears":4}
2             b               {"apples":4, "pears":4, "oranges":2}
3             c               {"apples":2, "oranges":3}
4             a               {"apples": 5}
5             c               {"oranges": 4}
6             a               {"oranges":1}

I need to check which customers have at some point ordered each product, apples, pears and oranges, so the output should be as follows:

customer_name
a
b

I have zero experience with accessing JSON objects in databases and I am wondering how I can retrieve a list of customers who have at some point placed an order for [list of keys].

Thanks in advance!

Upvotes: 0

Views: 220

Answers (2)

user330315
user330315

Reputation:

You can do this with a simple query if you are willing to create a custom aggregate function that aggregates multiple JSONB values into a single one, merging the keys.

First create the aggregate function:

create aggregate jsonb_object_agg(jsonb) 
(  
  sfunc = 'jsonb_concat',
  stype = jsonb,
  initcond = '{}'
);

Note that this is different then the built-in jsonb_object_agg() which takes two parameters (key, value). It uses the jsonb_concat function || to merge two JSONB values into one.

With that aggregate, your query is as simple as:

select customer_name
from orders
group by customer_name
having jsonb_object_agg(products_ordered) ?& array['pears','apples', 'oranges'];

The ?& operator checks if all keys are present in the JSONB value.

Without the aggregate function you can do this:

select customer_name
from orders, 
     jsonb_each_text(products_ordered) as t(k,v)
group by customer_name
having jsonb_object_agg(t.k, t.v) ?& array['pears','apples', 'oranges'];

However this expands each JSON value into multiple rows for every order, just to aggregate it back again - this produces a bigger intermediate result than the "real" aggregate function, so it might be slower.

Online example: http://rextester.com/EAZVG14239

All of the above assumes that your products_ordered is a JSONB column. If it's a JSON column, you need to cast it to JSONB

Upvotes: 0

Scoots
Scoots

Reputation: 3102

This isn't the prettiest query, but it'll work.

Extract the json keys into their own records, aggregate the records by customer name and then do an array-contains comparison. Somewhat abusing CTEs to do this though...

with
    __customer_products as(
        select
            customer_name,
            json_object_keys(products_ordered) as product_key
        from
            orders
    ),
    __customer_products_merged as(
        select
            customer_name,
            array_agg(product_key) as product_keys
        from
            __customer_products
        group by
            customer_name
    )
select
    customer_name
from
    __customer_products_merged
where
    product_keys @> array['apples', 'pears', 'oranges']

Upvotes: 1

Related Questions