Re: SQL/JSON: functions
Nikita Glukhov <n.gluhov@postgrespro.ru>
Attachments
- 0001-Implementation-of-SQL-JSON-path-language-v34.patch.gz (application/gzip) patch v34-0001
- 0006-Add-invisible-coercion-form-v34.patch (text/x-patch)
Attached 34th version of the patches.
On 16.02.2019 8:12, Andres Freund wrote:
> On 2018-12-05 02:01:19 +0300, Nikita Glukhov wrote:
>> + JsonLexContext *lex;
>> + JsonTokenType tok;
>> +
>> + lex = makeJsonLexContext(json, false);
>> +
>> + /* Lex exactly one token from the input and check its type. */
>> + PG_TRY();
>> + {
>> + json_lex(lex);
>> + }
>> + PG_CATCH();
>> + {
>> + if (ERRCODE_TO_CATEGORY(geterrcode()) == ERRCODE_DATA_EXCEPTION)
>> + {
>> + FlushErrorState();
>> + MemoryContextSwitchTo(mcxt);
>> + PG_RETURN_BOOL(false); /* invalid json */
>> + }
>> + PG_RE_THROW();
>> + }
>> + PG_END_TRY();
>
> It baffles me that a year after I raised this as a serious issue, in
> this thread, this patch still contains code like this.
PG_TRY/PG_CATCH was removed here: 'throwErrors' flag was added to JsonLexContext
instead.
Also usage of subtransactions is SQL/JSON functions (JsonExpr node) was
optimized: they can be not only omitted in ERROR ON ERROR case but also when
the resulting conversion from the SQL/JSON item type to the target SQL type is
no-op.
Below are the results of simple performance test
(operator #>> uses optimization which I recently posted in the separate patch):
query | time, ms
-------------------------------------------------------------------
JSON_VALUE(js, '$.x.y.z' RETURNING text) = '123' | 1923,360 (subtrans!)
JSON_VALUE(js, '$.x.y.z' RETURNING text
ERROR ON ERROR) = '123' | 970,604
JSON_VALUE(js, '$.x.y.z' RETURNING numeric) = '123' | 792,412
JSON_VALUE(js, '$.x.y.z' RETURNING numeric
ERROR ON ERROR) = '123' | 786,647
(js->'x'->'y'->'z') = '123' | 1104,470
(js->'x'->'y'->'z')::numeric = '123' | 940,037
(js->'x'->'y'->>'z') = '123' | 688,484
(js #> '{x,y,z}') = '123' | 1127,661
(js #> '{x,y,z}')::numeric = '123' | 971,931
(js #>> '{x,y,z}') = '123' | 718,173
Table with jsonb rows like '{"x": {"y": {"z": 123}}}':
CREATE TABLE t AS
SELECT JSON_OBJECT('x' : JSON_OBJECT('y' : JSON_OBJECT('z' : i))) js
FROM generate_series(1, 3000000) i;
Example query:
SELECT * FROM t WHERE JSON_VALUE(js, '$.x.y.z' RETURNING numeric) = '123';
--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
Commits
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
JSON_TABLE: Add support for NESTED paths and columns
- bb766cde63b4 17.0 landed
-
Add basic JSON_TABLE() functionality
- de3600452b61 17.0 landed
-
Add SQL/JSON query functions
- 6185c9737cf4 17.0 landed
-
Add soft error handling to some expression nodes
- aaaf9449ec6b 17.0 landed
-
Adjust populate_record_field() to handle errors softly
- 1edb3b491bee 17.0 landed
-
Refactor code used by jsonpath executor to fetch variables
- faa2b953ba3b 17.0 landed
-
Add more SQL/JSON constructor functions
- 03734a7fed7d 17.0 landed
-
SQL/JSON: support the IS JSON predicate
- 6ee30209a6f1 16.0 landed
-
SQL/JSON: add standard JSON constructor functions
- 7081ac46ace8 16.0 landed
-
Improve readability of SQL/JSON documentation.
- b46bcf7a4b6f 15.0 landed
-
Documentation for SQL/JSON features
- a6baa4baddd5 15.0 landed
-
RETURNING clause for JSON() and JSON_SCALAR()
- 49082c2cc3d8 15.0 landed
-
SQL JSON functions
- 606948b058dc 15.0 landed
-
SQL/JSON query functions
- 1a36bc9dba8e 15.0 landed
-
IS JSON predicate
- 33a377608fc2 15.0 landed
-
SQL/JSON constructors
- f4fb45d15c59 15.0 landed
-
Common SQL/JSON clauses
- f79b803dcc98 15.0 landed
- 865fe4d5df56 15.0 landed
-
Doc: remove obsolete entries in table of BRIN strategy numbers.
- 0ce4cd04da55 14.0 cited
-
Doc: standardize markup a bit more.
- 47046763c3ed 13.0 cited
-
Make pg_regress.c unset PGDATABASE during make installcheck.
- 5ee96b3e2221 13.0 cited
-
Fix handling of GENERATED columns in CREATE TABLE LIKE INCLUDING DEFAULTS.
- b81a9c2fc525 13.0 cited