JSON queries

[pbsds]

Believe it or not, many use SQL tables as a document store, dumping massive amounts of JSON into a column and querying these. Each SQL engine manipulates the JSON objects differently:

• postgres
• mysql
• sqlite

Postgres provides various homegrown infix operators to filter and query the JSON objects, while MySQL and Sqlite simply provide a bunch of functions that take the json blob as input.

• Should such a syntax be based on
  • postgres-like infix operators
  • functions like sqlite/mysql
  • C/python/javascript/rust inspired hashmap operators (a[b]), optionally with null-conditional and coalescing operators like in c# (a?[b ?? 0])
  • Or instead be a DSL inspired by jq
• Should PRQL even try to address JSON?

[RCHowell]

I would take a look at PartiQL / SQL++. It offers language extensions to SQL for semi-structured hierarchical data such as JSON. https://partiql.org/

[vanillajonathan]

Microsoft SQL Server have support for JSON too. https://learn.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver16