Thanks, that's a great question. You're right in that so far we haven't highlighted working with JSON and I hadn't actually tried until this point. IMHO the tru

Thanks, that's a great question. You're right in that so far we haven't highlighted working with JSON and I hadn't actually tried until this point. IMHO the true power of PRQL comes from the fact that it allows you to define functions and with that you get the power of composability which is the true power of almost every programming language (and which is for the most part completely lacking in SQL). So with that said, I tried the following POC (remember that PRQL is just a SQL generator so the JSON capabilities depend on your underlying RDBMS): ```sh > prqlc compile <<EOF let get = path obj -> s"""{obj} -> {path}""" let getstr = path obj -> s"""{obj} ->> {path}""" let extract = obj path -> s"""json_extract({obj}, {path})""" from [{data='{"duck": [1, 2, 3]}'}] select { data | get '$.duck[0]', data | getstr '$.duck[1]', extract data '$.duck[2]' } EOF WITH table_0 AS ( SELECT '{"duck": [1, 2, 3]}' AS data ) SELECT data -> '$.duck[0]', data ->> '$.duck[1]', json_extract(data, '$.duck[2]') FROM table_0 -- Generated by PRQL compiler version:0.9.4 (https://prql-lang.org) ``` What's going on here is that I used [s-strings](https://prql-lang.org/book/reference/syntax/s-strings.html https://prql-lang.org/book/reference/syntax/s-strings.html) to define custom PRQL functions `get`, `getstr` and `extract` which translate into the underlying `->`, `->>` and `json_extract` SQL constructs. You could then for example pipe that query to DuckDB (the example is taken from the following DuckDB blogpost [Shredding Deeply Nested JSON, One Vector at a Time](https://duckdb.org/2023/03/03/json.html https://duckdb.org/2023/03/03/json.html)): ```sh > prqlc compile <<EOF - | duckdb let get = path obj -> s"""{obj} -> {path}""" let getstr = path obj -> s"""{obj} ->> {path}""" let extract = obj path -> s"""json_extract({obj}, {path})""" from [{data='{"duck": [1, 2, 3]}'}] select { data | get '$.duck[0]', data | getstr '$.duck[1]', extract data '$.duck[2]'} EOF ┌───────────────────────┬──────────────────────────┬───────────────────────────────────┐ │ "data" -> '$.duck[0]' │ ("data" ->> '$.duck[1]') │ json_extract("data", '$.duck[2]') │ │ json │ varchar │ json │ ├───────────────────────┼──────────────────────────┼───────────────────────────────────┤ │ 1 │ 2 │ 3 │ └───────────────────────┴──────────────────────────┴───────────────────────────────────┘ ``` HTH