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 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) 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)):
```sh
> prqlc compile 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