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
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)