How to check if one json contains another json in snowflake

Question

The following doesn't work although I thought it would work

select contains(PARSE_JSON('{"id": 2, "bucket_type": 11}'), PARSE_JSON('{"id": 2}')) ;

The above query returns false.

Is there a way to do this ?

Answer 1

You can create a UDF that will resolve if something is_subset():

CREATE OR REPLACE FUNCTION is_subset("a" OBJECT, "b" OBJECT) 
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
isSubset = (superObj, subObj) => {
    return Object.keys(subObj).every(ele => {
        if (typeof subObj[ele] == 'object') {
            return isSubset(superObj[ele], subObj[ele]);
        }
        return subObj[ele] === superObj[ele]
    });
  // https://stackoverflow.com/a/61676007/132438
};
return isSubset(a,b);
$$;

And now we can test it:

SELECT is_subset(PARSE_JSON('{"id": 2, "bucket_type": 11}'), PARSE_JSON('{"id": 2}'));
-- true

SELECT is_subset(PARSE_JSON('{"id": 2}'), PARSE_JSON('{"id": 2, "bucket_type": 11}'));
-- false

Answer 2

please see if the below will work with you

with first_json
as
(select parse_json(column1) as src, src:id as id
from values('{"id": 2, "bucket_type": 11}')),
second_json
as
(select parse_json(column1) as src, src:id as id
from values('{"id": 2}'))
select 
case 
when f.id in(select id from second_json)
then 'true'
        else 'false'
    end as result
from first_json f;