What is the equivalent for JSONPath->$.*~ in duckDB?

Question

What is the equivalent for JSONPath->$.*~ in duckDB ?

Example:

{
    "firstName": "John",
    "lastName": "doe",
    "age": 26,
    "address": {
        "streetAddress": "naist street",
        "city": "Nara",
        "postalCode": "630-0192"
    },
    "phoneNumbers": [
        {
            "type": "iPhone",
            "number": "0123-4567-8888"
        },
        {
            "type": "home",
            "number": "0123-4567-8910"
        }
    ]
}

JSONPath $.*~ would fetch the following results...

[
  "firstName",
  "lastName",
  "age",
  "address",
  "phoneNumbers"
]

I tried this in duckdb...

CREATE or replace TABLE example (j JSON);
INSERT INTO example VALUES
('{ "firstName": "John","lastName": "doe","age": 26,  "address": { "streetAddress": "naist street","city": "Nara","postalCode": "630-0192"},"phoneNumbers": [ {"type": "iPhone", "number": "0123-4567-8888"},{"type": "home","number": "0123-4567-8910"     }    ]}')

select j->'$.*~' from example

but this throw the error.. SQL Error: java.sql.SQLException: Binder Error: JSON path error near '~'

Answer 1

There is the json_keys() function.

duckdb.sql("from example select json_keys(j)")

┌───────────────────────────────────────────────────┐
│                   json_keys(j)                    │
│                     varchar[]                     │
├───────────────────────────────────────────────────┤
│ [firstName, lastName, age, address, phoneNumbers] │
└───────────────────────────────────────────────────┘

The JSON functions are documented here:

• https://duckdb.org/docs/extensions/json#json-scalar-functions