JSON_EXISTS

Description

The JSON_EXISTS predicate determines whether JSON data contains a JSON value that can be located by using the specified sql-json-path-expression.

JSON_EXISTS is not supported in Snowflake.

Click here to navigate to the IBM DB2 docs page for this syntax.

Grammar Syntax

Sample Source Patterns

IBM DB2

Code

SELECT 
empno,
lastname 
FROM employee
WHERE NOT 
JSON_EXISTS(COALESCE(JSON_DATA, ''), 'strict $.emergency' FALSE ON ERROR);

Snowflake

Code

SELECT empno, lastname FROM employee
WHERE NOT /*** MSC-ERROR - MSCEWI1021 - JSON_EXISTS NOT SUPPORTED ***/
PUBLIC.JSON_EXISTS_UDF('JSON_EXISTS(COALESCE(JSON_DATA, \'\'), \'strict $.emergency\' FALSE ON ERROR)');

Related EWIs

1. MSCEWI1021: Not supported.

Recomendations

Posible Workarounds for JSON_EXISTS support below.

-- DB2 sql-json-path-expression to SnowFlake SQL
-- CREATE THE DEMO TABLE
create or replace table person (v variant);
-- POPULATE TABLE
insert into person select parse_json ('{ "isbn": "123-456-222", "author": [ { "name":"Jones"},{"name":"Smith"}]}');
-- QUERY TABLE
select
    *
from
    person
;
select
    v:isbn              -- $.isbn
    , v:author[0].name  -- $.author[0].name
    , v:author[1].name  -- $.author[1].name
from person
;
-- RECREATE THE DEMO TABLE FOR ANOTHER EXAMPLE
create or replace table person (v variant);
-- POPULATE TABLE
insert into person select parse_json ('{
  "person"   : {"firstname":"Fred", "lastname":"Gauss"},
  "where"    : "General Products",
  "friends"  : [ { "name": "Lili","rank": 5 }, {"name": "Hank", "rank": 7} ],
  "work.area": "Finance"
}');
-- QUERY TABLE
select
    *
from
    person
;
select
    v:person:lastname              -- $.person.lastname
    , v:friends                    -- $.friends
    , v:person:firstname           -- $.*.firstname     [not equal]
    , v:person                     -- $.person.*        [not equal]
    , v:friends                    -- $.friends[*]
    , CONCAT(v:friends[0]:rank, ', '
             , v:friends[1]:rank)  -- $.friends[*].rank [not equal]
    , v:work.area                  -- $.work.area
    , v:"work.area"                -- $.work\.area
from person
;
-- THIS IS A WORKAROUND FOR * WILDCARD, TO RETURN SEPARATED (FLATTEN) VALUES FROM AN ARRAY
select
    value:rank                     -- $.friends[*].rank
from
    person
    , lateral flatten( input => v:friends )
;
// DROP TABLE
DROP TABLE person
;