How I can search inside the json that is consisted of an array of a JSON into objects?

All we need is an easy explanation of the problem, so here it is.

Ι have the following table named discounts:

id Serialize
discount INT
offers JSON

With the following records

id discount offers
1 10 [{"type":"discount","percent":100},{"type":"freebie","id:100}]
2 0 [{"type":"discount","percent":100},{"type":"freebie","id:100}]
2 100 NULL

How I can search inside the table for all records that have non zero discount but inside the JSON that has type discount inside the JSON.

So far I have tried this query:

select * 
from discounts r 
where discount <> 0 
  and offers @> '[{"type":"discount"}]'::json;

But I get the following error:

ERROR: operator does not exist: json @> json

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Position: 68

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

CREATE TABLE discounts (id INT, discount INT, offers JSON);
INSERT INTO discounts VALUES
(1,   10, '[{"type":"discount","percent":100},{"type":"freebie","id":100}]'::JSON),
(2,   0,  '[{"type":"discount","percent":100},{"type":"freebie","id":100}]'::JSON),
(2,   100,    NULL);
SELECT * FROM discounts;
id discount offers
1 10 [{"type":"discount","percent":100},{"type":"freebie","id":100}]
2 0 [{"type":"discount","percent":100},{"type":"freebie","id":100}]
2 100 null
select * 
from discounts r 
where discount <> 0 
  and offers @> '[{"type":"discount"}]'::json;
ERROR:  operator does not exist: json @> json
LINE 4:   and offers @> '[{"type":"discount"}]'::json;
                     ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
select * 
from discounts r 
where discount <> 0 
  and offers::JSONB @> '[{"type":"discount"}]'::JSONB;
id discount offers
1 10 [{"type":"discount","percent":100},{"type":"freebie","id":100}]

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply