Are these methods to read json equivalent?

usertIwP 0
Viewed 21

if you have data stored as json in a column, you could query the data by

select * from TABLE where json_column['name'] = 'Bob'

or

select * from TABLE where parse_json(json_column::variant):name:varchar = 'Bob'

Are these equivalent in performance?

snowflake-cloud-data-platform
2 Answers

No, they are not equivalent in performance, although they use a very similar path to access the "name" field.

  • The first query can use metadata of JSON attributes, and you may see a good partition pruning.
  • The second one will read all JSON data (whole partitions) and use parse_json on the column before fetching the field.

In short, the first query is expected to be much faster on larger datasets.

Gokhan Atil0

Gokhan, is very correct, that parsing the json a second time is a complete waste of time, and given the JSON/VARIANT data is put into a auto-magic columnar form, this will kill performance.

But while we are looking at ways to write the same SQL, you can access the "name" of the json, three ways

with table_name(json_column) as (
    select parse_json(column1) 
    from values
    ('{"name":"not_bob","val1":10, "val2":"text tokens"}'),
    ('{"name":"Bob","val1":11, "val2":"more text"}'),
    ('{"name":"Cat","val1":"not a number", "val2":"less"}')
)
select *
    ,json_column['name'] as name_1
    ,json_column:name as name_2
    ,get(json_column, 'name') as name_3
    ,parse_json(json_column) as json_2
    ,json_2['name'] as name_2_1
    ,json_2:name as name_2_2
    ,get(json_2, 'name') as name_2_3
from table_name 
--where json_column['name'] = 'Bob'
;
JSON_COLUMN NAME_1 NAME_2 NAME_3 JSON_2 NAME_2_1 NAME_2_2 NAME_2_3
{ "name": "not_bob", "val1": 10, "val2": "text tokens" } "not_bob" "not_bob" "not_bob" { "name": "not_bob", "val1": 10, "val2": "text tokens" } "not_bob" "not_bob" "not_bob"
{ "name": "Bob", "val1": 11, "val2": "more text" } "Bob" "Bob" "Bob" { "name": "Bob", "val1": 11, "val2": "more text" } "Bob" "Bob" "Bob"
{ "name": "Cat", "val1": "not a number", "val2": "less" } "Cat" "Cat" "Cat" { "name": "Cat", "val1": "not a number", "val2": "less" } "Cat" "Cat" "Cat"
Simeon Pilgrim0
Related