Skip site navigation (1) Skip section navigation (2)

Re: SQL/JSON in PostgreSQL

From: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
To: Piotr Stefaniak <email(at)piotr-stefaniak(dot)me>, "obartunov(at)gmail(dot)com" <obartunov(at)gmail(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>, Teodor Sigaev <teodor(at)postgrespro(dot)ru>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, andrew Dunstan <andrew(at)dunslane(dot)net>
Subject: Re: SQL/JSON in PostgreSQL
Date: 2017-11-03 11:29:33
Message-ID: b66123f5-c26e-438b-daff-be3675d080de@postgrespro.ru (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-hackers
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

Attachment: 0001-Allow-variable-jsonpath-specifications.patch
Description: text/x-patch (12.1 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Michael PaquierDate: 2017-11-03 11:53:42
Subject: Re: postgres_fdw: Add support for INSERT OVERRIDING clause
Previous:From: Michael PaquierDate: 2017-11-03 11:10:14
Subject: Re: [bug fix] postgres.exe crashes with access violation on Windows while starting up

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group