Re: JSONB order?

On Thu, Nov 5, 2020 at 4:35 PM Tony Shelver <tshelver@gmail.com> wrote:
>
> I am getting data out of a spreadsheet (Google API) and loading it into a Python 3.8 dict.
> I then dump it to json format. On printing, it's in the correct order:
> {
> "Timestamp": "05/11/2020 17:08:08",
> "Site Name": "SureSecurity Calgary",
> "Last Name": "Shelver",
> "First Name": "Anthony",
> "Middle Name(s)": "",
> "Phone": 555757007,
>  "Person visited": "test",
>  "Body Temperature": 44,
>  "Fever or chills": "No",
>  "Difficulty breathing or shortness of breath": "No",
>  "Cough": "No",
>  "Sore throat, trouble swallowing": "No",
>  "Runny nose/stuffy nose or nasal congestion": "No",
>  "Decrease or loss of smell or taste": "No",
>  "Nausea, vomiting, diarrhea, abdominal pain": "No",
>  "Not feeling well, extreme tiredness, sore muscles":
>  "Yes", "Have you travelled outside of Canada in the past 14 days?": "No",
>  "Have you had close contact with a confirmed or probable case of COVID-19?": "No"
>  }
>
> It's passed to a plpgsql function, using a jsonb parameter variable.
> This insets it into the table, into into a jsonb column.
>
> When looking at what the column contents are, it's been rearranged.  The order always seems to have been rearranged
inthe same way, as below:
 
> {
>     "Cough": "No",
>     "Phone": 5555757007,
>     "Last Name": "Shelver",
>     "Site Name": "SureSecurity Calgary",
>     "Timestamp": "04/11/2020 17:34:48",
>     "First Name": "Anthony",
>     "Middle Name(s)": "",
>     "Person visited": "Many",
>     "Fever or chills": "No",
>     "Body Temperature": 44,
>     "Sore throat, trouble swallowing": "No",
>     "Decrease or loss of smell or taste": "No",
>     "Nausea, vomiting, diarrhea, abdominal pain": "No",
>     "Runny nose/stuffy nose or nasal congestion": "No",
>     "Difficulty breathing or shortness of breath": "No",
>     "Not feeling well, extreme tiredness, sore muscles": "No",
>     "Have you travelled outside of Canada in the past 14 days?": "No",
>     "Have you had close contact with a confirmed or probable case of COVID-19?": "No"
> }
>
> If the order had remained the same, it's child's play to pull the data out and present it in a report, even if the
dataelements change.
 
> But...  seen above, the order gets mixed up.
>
> Any ideas?

The json standard declares that the keys in a document are unordered,
and can appear at any order.

In PostgreSQL, jsonb will not preserve key ordering,  as a feature for
efficiency. The plain json datatype will, so if key ordering is
important you should use json instead of jsonb (but you should
probably also not use the json format in general, as it does not
guarantee this)

See https://www.postgresql.org/docs/13/datatype-json.html

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/