4 ms·
> PostgreSQL 12 now allows execution of JSON path queries per the SQL/JSON specification in the SQL:2016 standard. Similar to XPath expressions for XML, JSON pa
by GordonS 7y ago
> PostgreSQL 12 now allows execution of JSON path queries per the SQL/JSON specification in the SQL:2016 standard. Similar to XPath expressions for XML, JSON path expressions let you evaluate a variety of arithmetic expressions and functions in addition to comparing values within JSON documents.
I'm not familiar with JSON path queries - how do they compare to the jsonb querying syntax that Postgres already has?
- anarazel 7y agojsonpath does allow a lot more complicated expressions than before. See https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH https://www.postgresql.org/docs/devel/functions-json.html#FU... for example, and also https://www.depesz.com/2019/03/19/waiting-for-postgresql-12-partial-implementation-of-sql-json-path-language/ https://www.depesz.com/2019/03/19/waiting-for-postgresql-12-...
- nerdwaller 7y agoIt looks like they’re closer to what you’d use in `jq` (or loosely similar to Xpath if that’s familiar): https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH https://www.postgresql.org/docs/devel/functions-json.html#FU...
- xkcd-sucks 7y agoOne thing JSONpath does is picking properties from multiple elements of an array, like `$.piff[*].puff` . Postgres jsonb query syntax has no operations on JSONB arrays so you have to do some complicated stuff in order to make this query. Also, the syntax is standardized so people have probably encountered it before in e.g. aws cloudwatch JSON metric filters