Json paths

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Json paths

Charles Leifer
Many of the sqlite json1 functions accept a path parameter, which the
documents describe as:

For functions that accept PATH arguments, that PATH must be well-formed or
else the function will throw an error. A well-formed PATH is a text value
that begins with exactly one '$' character followed by zero or more
instances of ".objectlabel" or "[arrayindex]".

I was wondering if there were any plans to support wildcard paths?

Postgres v12 release looks like it has a pretty sophisticated jsonpath
type. Examples:

SELECT jsonb_path_query(x, '$.** ? (@.name == "a")') FROM test;
 SELECT jsonb_path_query(x, '$.sizes[1 to last]') FROM test;


MySQL allows wildcards in a couple flavors.

Do you anticipate anything like this landing in sqlite's json extension?

Thank you for the wonderful software,

Charles
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Json paths

Dominique Devienne
On Mon, Apr 15, 2019 at 6:34 AM Charles Leifer <[hidden email]> wrote:

> I was wondering if there were any plans to support wildcard paths?
>

The main issue here IMHO is that there's no official standard, AFAIK.


> Postgres v12 release looks like it has a pretty sophisticated jsonpath
> type.


SQLite does often follow PG's lead, so maybe that's the path forward.

Haven't looked up the PG 12's JsonPath doc yet, especially since release
later this year only.

Here's one of the many JsonPath flavor I found:
https://restfulapi.net/json-jsonpath/

I'm used XPath2 (within XSLT2) extensively years ago, which is an actual
standard,
and has Michael Kay's [1] excellent book to explain it [2], but so such
resources around "JsonPath".

Again, doesn't prevent "inventing" one's semantic, or following an existing
one. Still, it's unfortunate. --DD

[1] https://en.wikipedia.org/wiki/Michael_Howard_Kay
[2]
https://www.amazon.com/XSLT-2-0-XPath-Programmers-Reference/dp/0470192747
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Json paths

Charles Leifer
I still think that, in spite of the absence of a clear standard, sqlite
could benefit from more sophisticated path support.

Sqlite's json functions seem modeled after the MySQL json functions. Since
MySQL supports some nice features (wildcard, prefix/suffix match), it does
seem reasonable that sqlite might support similar behavior.

And, as you mentioned, sqlite tends to follow postgres as a kind of
standard, so once 12.0 lands it would be cool to see sqlite offer similar
json path support.

Anyway, I understand that there are perhaps many other things that are much
higher priority. Love the window functions, on conflict, etc which have
been added in the last few releases. Amazing stuff. Just wanted to see if
json path would be even on the road map.

On Mon, Apr 15, 2019, 4:57 AM Dominique Devienne <[hidden email]>
wrote:

> On Mon, Apr 15, 2019 at 6:34 AM Charles Leifer <[hidden email]> wrote:
>
> > I was wondering if there were any plans to support wildcard paths?
> >
>
> The main issue here IMHO is that there's no official standard, AFAIK.
>
>
> > Postgres v12 release looks like it has a pretty sophisticated jsonpath
> > type.
>
>
> SQLite does often follow PG's lead, so maybe that's the path forward.
>
> Haven't looked up the PG 12's JsonPath doc yet, especially since release
> later this year only.
>
> Here's one of the many JsonPath flavor I found:
> https://restfulapi.net/json-jsonpath/
>
> I'm used XPath2 (within XSLT2) extensively years ago, which is an actual
> standard,
> and has Michael Kay's [1] excellent book to explain it [2], but so such
> resources around "JsonPath".
>
> Again, doesn't prevent "inventing" one's semantic, or following an existing
> one. Still, it's unfortunate. --DD
>
> [1] https://en.wikipedia.org/wiki/Michael_Howard_Kay
> [2]
> https://www.amazon.com/XSLT-2-0-XPath-Programmers-Reference/dp/0470192747
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users