Re: SQL/JSON: functions

Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>

From: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>

To: Andrew Dunstan <andrew@dunslane.net>

Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Dmitry Dolgov <9erthalion6@gmail.com>, Oleg Bartunov <obartunov@postgrespro.ru>, Erik Rijkers <er@xs4all.nl>

Date: 2022-01-05T05:51:44Z

Lists: pgsql-hackers

On Thu, Dec 9, 2021 at 7:34 PM Himanshu Upadhyaya
<upadhyaya.himanshu@gmail.com> wrote:
> 3)
> Is not that result of the two below queries should match because both are trying to retrieve the information from the JSON object.
>
> postgres=# SELECT JSON_OBJECT('track' VALUE '{
>     "segments": [
>       {
>         "location":   [ 47.763, 13.4034 ],
>         "start time": "2018-10-14 10:05:14",
>         "HR": 73
>       },
>       {
>         "location":   [ 47.706, 13.2635 ],
>         "start time": "2018-10-14 101:39:21",
>         "HR": 135
>       }
>     ]
>   }
> }')->'track'->'segments';
>  ?column?
> ----------
>
> (1 row)
>
> postgres=# select '{
>   "track": {
>     "segments": [
>       {
>         "location":   [ 47.763, 13.4034 ],
>         "start time": "2018-10-14 10:05:14",
>         "HR": 73
>       },
>       {
>         "location":   [ 47.706, 13.2635 ],
>         "start time": "2018-10-14 10:39:21",
>         "HR": 135
>       }
>     ]
>   }
> }'::jsonb->'track'->'segments';
>                                                                              ?column?
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}]
> (1 row)
>
just wanted to check your opinion on the above, is this an expected behaviour?

> Few comments For 0002-SQL-JSON-constructors-v59.patch:
Also, any thoughts on this?

-- 
Regards,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com

Commits

Same data as JSON: 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