All we need is an easy explanation of the problem, so here it is.
I had heard that in postgresql, the json type preserves declared ordering unlike it’s fully-parsed counterpart jsonb.
I want to get the ordinal position of each key in a document as supplied by the client. From what I’ve tried though, this doesn’t seem possible.
select *
from jsonb_path_query(
'[{"foo":1},{"foo":2,"bar":3}]'::jsonb,
'$.*'::jsonpath
) with ordinality
| jsonb_path_query | ordinality |
|---|---|
| 1 | 1 |
| 3 | 2 |
| 2 | 3 |
The preceding query shows me leaf node value 3 at ordinal position 2 despite it being supplied as the third key, but there doesn’t appear to be a json_path_query companion to jsonb_path_query.
Is there a built-in function to parse json keys? Or am I limited to either stricter jsonb or completely unconstrainted text parsing to extract this information?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
json_object_keys() can serve this use case.
Note in the following example that the jsonb input reliably returns with sorted and deduplicated keys while json version gives us all keys as supplied by the client with correct ordinality.
select *
from json_object_keys('{"foo":2,"bar":3,"foo":4}'::json)
with ordinality
| json_object_keys | ordinality |
|---|---|
| foo | 1 |
| bar | 2 |
| foo | 3 |
select *
from jsonb_object_keys('{"foo":2,"bar":3,"foo":4}'::jsonb)
with ordinality
| json_object_keys | ordinality |
|---|---|
| bar | 1 |
| foo | 2 |
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0