Re: SQL/JSON in PostgreSQL

On 03.11.2017 00:32, Piotr Stefaniak wrote:

> On 2017-02-28 20:08, Oleg Bartunov wrote:
>> The standard describes SQL/JSON path language, which used by SQL/JSON query
>> operators to query JSON. It defines path language as string literal. We
>> implemented the path language as JSONPATH data type, since other
>> approaches are not friendly to planner and executor.
> I was a bit sad to discover that I can't
> PREPARE jsq AS SELECT JSON_QUERY('{}', $1);
> I assume because of this part of the updated grammar:
> json_path_specification:
> Sconst { $$ = $1; }
> ;
>
> Would it make sense, fundamentally, to allow variables there? After
> Andrew Gierth's analysis of this grammar problem, I understand that it's
> not reasonable to expect JSON_TABLE() to support variable jsonpaths, but
> maybe it would be feasible for everything else? From Andrew's changes to
> the new grammar (see attached) it seems to me that at least that part is
> possible. Or should I forget about trying to implement the other part?
By standard only string literals can be used in JSON path specifications.
But of course it is possible to allow to use variable jsonpath
expressions in
SQL/JSON functions.

Attached patch implements this feature for JSON query functions,
JSON_TABLE is
not supported now because it needs some refactoring.

I have pushed this commit to the separate branch because it is not
finished yet:
https://github.com/postgrespro/sqljson/tree/sqljson_variable_json_path

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company