4 ms·
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 i
by kls 15y ago
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.
- samstokes 15y agoYou wouldn't even need an extension to the SQL parser - you could just expose a SQL function to do the JSONPath query. e.g. SELECT users.id, jsonpath('$.timezone', users.preferences_json) AS tz FROM users WHERE tz LIKE 'America/%';
- jaylevitt 15y agoThat'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.
- pilif 15y ago> 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)