Re: Tackling JsonPath support

Hi

2016-11-13 15:14 GMT+01:00 Christian Convey <christian(dot)convey(at)gmail(dot)com>:

> Hi Pavel,
>
> Can I check a few assumptions about what you're suggesting for this task?
>
> * Our ultimate goal is to give Postgres an implementation of the functions
> "JSON_EXISTS", "JSON_VALUE", and "JSON_QUERY" which fully comply with the
> SQL standards.
>
> * The best representation of those standards is found here: [1].
>
> * When [1] mentions a "JSON path expression" or "JSON path language", it's
> referring to the query language described here: [2].
>
> * Even if other popular DBMS's deviate from [1], or other popular JSONPath
> implementations deviate from [2], we remain committed to a faithful
> implementation of [1].
>
> * It's okay for my first commit to implement just two things: (a) a
> PG-internal implementation of JsonPath, and (b) a user-visible
> implementation of "JSON_QUERY" based on (a). Later commits could add
> implementations of "JSON_VALUE", "JSON_EXISTS", etc. in terms of (a).
>

My goal is implementation of JSON_TABLE function - this function can be
used instead any other mentioned function (and it is really useful - it is
usual task - transform JSON to table). The SQL/JSON is pretty new and
bigger for implementation in one step. Nobody knows it from PostgreSQL
world. The our SQL/XML needed more than 10 years and still is not fully
complete - and we used power and features libxml2 (nothing similar we have
for JSON). But almost what is daily need from SQL/XML we have. For
JSON_TABLE we need only basic features of JSONPath - the predicates are not
necessary in first step.

http://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/json/src/tpc/db2z_bif_jsontable.html
The vendors use name for this query language "SQL/JSON path expressions" -
so important source is SQL/JSON (this can be different than origin JSONPath
(your second source)).

Regards

Pavel

Regards

Pavel

> Thanks,
> Christian
>
> [1] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-
> Tutorial-Opening-Plenary.pdf
>
> [2] http://goessner.net/articles/JsonPath
>
>
> On Fri, Sep 16, 2016 at 2:28 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> Hi
>>
>> 2016-09-15 18:05 GMT+02:00 Christian Convey <christian(dot)convey(at)gmail(dot)com>:
>>
>>> On Mon, Sep 5, 2016 at 1:44 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>>> wrote:
>>> ...
>>>
>>> > I wrote XMLTABLE function, and I am thinking about JSON_TABLE
>>> function. But
>>> > there is one blocker - missing JsonPath support in our JSON
>>> implementation.
>>> >
>>> > So one idea - implement JsonPath support and related JSON query
>>> functions.
>>> > This can help with better standard conformance.
>>>
>>> Hi Pavel,
>>>
>>> Are you still looking for someone to add the JsonPath support to the
>>> JSON implementation? And if so, how urgently are people waiting for
>>> it?
>>>
>>
>> yes - JsonPath support should be great. I hope so this or next commitfest
>> the XMLTABLE patch will be committed, and with JsonPath I can start to work
>> on JSON_TABLE function.
>>
>> But the JsonPath can be merged separately without dependency to
>> JSON_TABLE. There are more JSON searching functions, and these functions
>> should to support JsonPath be ANSI SQL compliant.
>>
>>
>>>
>>> I'd be happy to start working on a patch, but since I'm new to PG
>>> development, I'm probably not the fastest person to get it done.
>>>
>>
>> It is not problem. Probably you should to do this work without deep
>> knowledges about PostgreSQL internals. The work with data types (and
>> functions for data types) is well isolated from PostgreSQL engine.
>>
>> You can learn from current searching on JSON -
>> postgresql/src/backend/utils/adt/json.c
>>
>> And it is good start to be PostgreSQL's hacker - I started with
>> implementation of own data type and related functions.
>>
>> Regards
>>
>> Pavel
>>
>>
>>> Kind regards,
>>> Christian
>>>
>>
>>
>