Subject: Re: SQL/JSON: functions
From: Andrew Alsup <bluesbreaker@gmail.com>
To: Nikita Glukhov <n.gluhov@postgrespro.ru>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <0c6e3740-7774-2d60-5eaa-c61bb5c1f2e0@gmail.com>
In-Reply-To: <0c6e3740-7774-2d60-5eaa-c61bb5c1f2e0@gmail.com>
Cc: Dmitry Dolgov <9erthalion6@gmail.com>,
 Pavel Stehule <pavel.stehule@gmail.com>,
 Oleg Bartunov <obartunov@postgrespro.ru>,
 Alexander Korotkov <a.korotkov@postgrespro.ru>
Message-ID: <8b75b660-9ea3-adbe-951c-2aa3a5f1801f@gmail.com>
Date: Fri, 4 Jan 2019 00:13:37 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:60.0)
 Gecko/20100101 Thunderbird/60.4.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

 > Attached patches implementing all SQL/JSON functions excluding 
JSON_TABLE:
 >
 > JSON_OBJECT()
 > JSON_OBJECTAGG()
 > JSON_ARRAY()
 > JSON_ARRAYAGG()
 >
 > JSON_EXISTS()
 > JSON_VALUE()
 > JSON_QUERY()

Sorry if this is a stupid question, but is this patch intended to 
implement any SQL/JSON functions? I'm basing this question on the 
original patch post (quoted above). The patch appears to be focused 
exclusively on "jsonpath expressions". I only ask because I think I 
misinterpreted and spent some time wondering if I had missed a patch file.

So far, the jsonpath docs are very readable; however, I think a few 
complete examples (full SELECT statements) of using jsonpath expressions 
would be helpful to someone new to this technology. Does postgresql 
provide a sample schema, similar to the Or*cle scott/tiger (emp/dept) 
schema, we could use for examples? Alternatively, we could reference 
something mentioned at 
https://wiki.postgresql.org/wiki/Sample_Databases. I think it would be 
nice if all the docs referenced the same schema, when possible.

For tests, would it be helpful to have some tests that 
demonstrate/assert equality between "jsonb operators" and "jsonpath 
expressions"? For example, using the existing regression test data the 
following should assert equality in operator vs. expression:

SELECT
   CASE WHEN jop_count = expr_count THEN 'pass' ELSE 'fail' END
FROM
   (
     -- jsonb operator
     SELECT count(*)
     FROM testjsonb
     WHERE j->>'abstract' LIKE 'A%'
   ) as jop_count,
   (
     -- jsonpath expression
     SELECT count(*)
     FROM testjsonb
     WHERE j @? '$.abstract ? (@ starts with "A")'
   ) as expr_count;