Cannot extract Json value from json column

[DenGodunov]

Hello! Having column value {"birth_date": {"$date": "2024-06-13T06:06:04.964Z"}} of type JSON I want to extract value of key $date in SELECT statement using Trino JSON functions.

You can reproduce my use case usind docker:
docker run --rm -d --name trino -p 8080:8080 trinodb/trino

docker exec -it trino trino

trino> use memory.default;
USE
trino:default> CREATE TABLE test1 (a json);
CREATE TABLE
trino:default> INSERT INTO test1
json '{"birth_date": {"$date": "2024-06-13T06:06:04.964Z"}}'

trino>
SELECT typeof(a) as type_of_a,
       a,

      json_query(a, 'strict $.birth_date."$date"' omit quotes) as my_b_day
FROM test1
;
-- Query 20240724_171115_00012_jf854 failed: line 4:18: Cannot read input of type json as JSON using formatting JSON

trino>
SELECT 
       a,
      json_extract_scalar(a, '$.birth_date."$date"') AS my_b_date_1
FROM test1
;
-- Query 20240724_171155_00013_jf854 failed: Invalid JSON path: '$.birth_date."$date"'

trino>
SELECT 
       a,
      json_extract(a, '$.birth_date."$date"') AS my_b_date_1
FROM test1
;
-- Query 20240724_171250_00015_jf854 failed: Invalid JSON path: '$.birth_date."$date"'

Every query returns error, please suggest right way to accomplish my task - extract JSON object value from my example JSON.

[martint]

For json_extract/json_extract_scalar, you need to change the JSON path to $.birth_date["$date"]. It doesn't support quoted path elements.

json_query doesn't yet support JSON type as input. It needs to operate on a VARCHAR. To get around that limitation, you can first convert the JSON value to VARCHAR via json_format:

json_query(json_format(a), 'strict $.birth_date."$date"' omit quotes)

[DenGodunov]

Thank you, your solutions works. Do we have these approaches in https://trino.io/docs/current/index.html?

[martint]

We should document that better. cc @mosabua