Issue when extracting JSON

[Greeengooo]

Some json contains the field with name “config/web”.
I have the following path inside JSON_EXTRACT_SCALAR
$.config/web.properties.minTlsVersion
When I try to query the data I receive the following error:
Invalid JSON path

In my opinion the problem could be with slash but I am not sure. Looking forward to getting some help on this.
Thanks

[ebyhr]

Try square brackets and double quotes.

SELECT json_extract('{"config/web":123}', '$["config/web"]');
 _col0
-------
 123

[sbbagal13]

Hi @ebyhr , How can I extract value if its nested json
e.g
JSON : {data:{"primary-rerun-idv": false}}

Below query return NULL
select json_extract_scalar('{data:{"primary-rerun-idv": false}}' ,'$["data.primary-rerun-idv"]') as val ;

[martint]

Use the json_value function:

SELECT json_value('{"data":{"primary-rerun-idv": false}}', 'strict $.data."primary-rerun-idv"');

See https://trino.io/docs/current/functions/json.html?#json-value for more details.

[sbbagal13]

Thanks @martint , It gave me some idea
below syntax worked for my use case:
select json_extract_scalar(json_data,'$.blendMsg.data["primary-rerun-idv"]') as val

somehow it doesn't work with single nested json {"data":{"primary-rerun-idv": false}}"

[lucas-0liveira]

The field with the name 'config/web' is directly in the root object, you can try accessing it using $.config/web. Then, to retrieve the property minTlsVersion, you could use

"$.config/web.properties.minTlsVersion"

If this doesn't work, it may be necessary to escape the forward slash ('/') in some way, depending on the language or tool you're using to extract the JSON value. For example, in some languages, you may need to use $.config/web.properties.minTlsVersion to escape the slash."

Hope I've helped