SQL - escape "-" special character // json_extract with date as key

Question

I'm running a query on exasol - trying to unpack data from a column.

Table has this format

KPI           Progress_history
A             {'2020-01-01': 100, '2020-02-01': 120}

my attempt referencing the documentation

select
    kpi,
    progress_history,
    json_extract(
        progress_history,
        '$.""2020-01-01"" LOG ON ERROR',
        '$.error()'
    )
    emits(
        "2020-01-01" int,
        error_column varchar(2000000)
    )
from kpis

output is either

SQL error: data exception - invalid JSON path: end of path expected at path 1, position 5: '$.""2020-'

or

"[{ ""error"" : ""data exception - invalid JSON text: The name of an object member must be a string (surrounded by quotation marks) at position 2: '{'2020'"" }]"

how can I fix this? I've tried '$.""2020\\-01\\-01"", '$."2020\\-01\\-01", '$.""2020\-01\-01"" ...

TLDR; I think my json path is incorrect because of a special character that I'm trying to escape.

Your TLDR seems to be correct. Try to replace single quotes with double quotes in json. — GriGrim, Nov 07 '22 at 22:27

score 1 · Accepted Answer · answered Nov 08 '22 at 09:40

Json only allows double quotes to delimit strings and the keys of an object must be a string. This is different to the object notation in e.g Javascript or Python where both single or double quotes are allowed for strings and keys in objects (or dicts for Python) can be any type. Some Json parsers are pretty lax about this and also allow single quotes or unquoted keys, but Exasol strictly adheres to the json specification. In your example you have to replace some single quotes by double quotes:

create schema s;
create or replace table kpis as (select 'Progress_history' as kpi, '{"2020-01-01": 100, "2020-02-01": 120}' as progress_history);
select
    kpi,
    progress_history,
    json_extract(
        progress_history,
        '$."2020-01-01" LOG ON ERROR',
        '$.error()'
    )
    emits(
        "2020-01-01" int,
        error_column varchar(2000000)
    )
from kpis;

-- You can check if Exasol considers a string valid json with the IS JSON predicate
-- Only the last string is valid json in this case
select '{''a'': 1}' is json, '{a:1}' is json, '{"a" : 1 }' is json;

Side note: It's even more confusing that in SQL itself, single quotes delimit string literals and double quotes delimit identifiers (which are not string literals!) See: What is the difference between single and double quotes in SQL?

thanks for detailed explanation, so there was no escaping the character I have to replace the single quotes `replace(k.progress_history, '''', '"') as progress_cleaned` and then run your query. — Adav, Nov 08 '22 at 10:49

SQL - escape "-" special character // json_extract with date as key

1 Answers1