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