Horton
Menu

Re: SQL/JSON: functions

Andrew Dunstan <andrew@dunslane.net>

View thread
From: Andrew Dunstan <andrew@dunslane.net>
To: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>
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-05T17:03:15Z
Lists: pgsql-hackers 
On 1/5/22 00:51, Himanshu Upadhyaya wrote:
> 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?


Your VALUE clause is actually not legal JSON - it has one too many
braces at the end. The reason postgres didn't complain about it is that
JSON_OBJECT is treating it as a string. If you correct the JSON and cast
it as jsonb you get the desired result:

andrew=# 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
      }
    ]
  }'::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 101:39:21"}]
(1 row)


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



I will look at that separately.


cheers


andrew


--
Andrew Dunstan
EDB: https://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 →

  1. JSON_TABLE: Add support for NESTED paths and columns

  2. Add basic JSON_TABLE() functionality

  3. Add SQL/JSON query functions

  4. Add soft error handling to some expression nodes

  5. Adjust populate_record_field() to handle errors softly

  6. Refactor code used by jsonpath executor to fetch variables

  7. Add more SQL/JSON constructor functions

  8. SQL/JSON: support the IS JSON predicate

  9. SQL/JSON: add standard JSON constructor functions

  10. Improve readability of SQL/JSON documentation.

  11. Documentation for SQL/JSON features

  12. RETURNING clause for JSON() and JSON_SCALAR()

  13. SQL JSON functions

  14. SQL/JSON query functions

  15. IS JSON predicate

  16. SQL/JSON constructors

  17. Common SQL/JSON clauses

  18. Doc: remove obsolete entries in table of BRIN strategy numbers.

  19. Doc: standardize markup a bit more.

  20. Make pg_regress.c unset PGDATABASE during make installcheck.

  21. Fix handling of GENERATED columns in CREATE TABLE LIKE INCLUDING DEFAULTS.