3 ms·

(I couldn't help but notice you didn't comment on the difference in formatting in the examples.) Do you have examples of PRQL working with jsonpath? Generating

by ttfkam 3y ago

(I couldn't help but notice you didn't comment on the difference in formatting in the examples.) Do you have examples of PRQL working with jsonpath? Generating JSON? Unnesting arrays? Returning ids from an INSERT or UPDATE without making a separate read query? Not trying to be argumentative. Honest question.

remram 3y ago
I am not affiliated in any way with the PRQL project. Those are great questions though, I hope we get an answer.
- snthpy 3y ago
  @remram I replied to the parent comment.
snthpy 3y ago
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
- ttfkam 3y ago
  Excellent, thank you for the response.

by ttfkam 3y ago

remram 3y ago

I am not affiliated in any way with the PRQL project. Those are great questions though, I hope we get an answer.

snthpy 3y ago
@remram I replied to the parent comment.

snthpy 3y ago

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

ttfkam 3y ago
Excellent, thank you for the response.