What would be interesting would be an extension to the SQL parser that supports JSONPath in SQL queries. like a WHERE JSON_COLUMN = some JSONPath query. Where it would act like a WHERE IN clause and could use a JSON style query syntax. I see no need to create a whole new construct when a valid one is available at this point. The two could be blended and it would create a very powerful way to query JSON data types in a relational system.
That's not going to help when you need to restrict your query with JSON, though; the select list is processed after the whole FROM clause, including JOINs, so you'd be producing the whole table and then throwing away whatever didn't match.
In general, a few core limitations of the current optimizer - lack of LATERAL key among them - make it impossible to create a PostgreSQL function that blends well with SQL, AFAICT.
> select uid from users wehere jsonpath('$.country', address) = 'CH'
PostgreSQL does support functional indexes, so you can easily make this use indexes and thus be fast enough for real use (it's a bad example - I'd probably put the address into an addresses table, but it's enough to illustrate the point)
Comments
What would be interesting would be an extension to the SQL parser that supports JSONPath in SQL queries. like a WHERE JSON_COLUMN = some JSONPath query. Where it would act like a WHERE IN clause and could use a JSON style query syntax. I see no need to create a whole new construct when a valid one is available at this point. The two could be blended and it would create a very powerful way to query JSON data types in a relational system.
You wouldn't even need an extension to the SQL parser - you could just expose a SQL function to do the JSONPath query. e.g.
That's not going to help when you need to restrict your query with JSON, though; the select list is processed after the whole FROM clause, including JOINs, so you'd be producing the whole table and then throwing away whatever didn't match.
In general, a few core limitations of the current optimizer - lack of LATERAL key among them - make it impossible to create a PostgreSQL function that blends well with SQL, AFAICT.
> select uid from users wehere jsonpath('$.country', address) = 'CH'
PostgreSQL does support functional indexes, so you can easily make this use indexes and thus be fast enough for real use (it's a bad example - I'd probably put the address into an addresses table, but it's enough to illustrate the point)