Thread: JSONB order?

> On Nov 5, 2020, at 07:34, Tony Shelver <tshelver@gmail.com> wrote:
> But...  seen above, the order gets mixed up.
>
> Any ideas?

JSON objects, like Python dicts, are not automatically ordered by key.  Once you move from the column space to the JSON
objectspace, you can't rely on the object keys being in a consistent order. 

You'll want to have a step when ingesting the JSON object into a report that lines up the key values appropriately with
theright presentation in the report. 
--
-- Christophe Pettus
   xof@thebuild.com

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/

> On Nov 5, 2020, at 07:45, Tony Shelver <tshelver@gmail.com> wrote:
> Thanks Christophe, that's what I thought.
> Just seemed weird that they were 'disordered' in exactly the same way every time.
>
> FYI, as of Python 3.7, dicts are ordered.
>
> The problem is that we are possibly going to have many versions of these forms with slightly differing keys, which
willbe a pain to order in some hard coded way. 

As Magnus noted, you can use JSON instead of JSONB.  JSON is basically a text blob with a syntax check wrapper around
it,so it will be order-stable once created.  (If you run it through a JSONB-expecting function, then the ordering may
changeagain.)  It's less efficient to operate on than JSONB, but that might be OK for your purposes. 

--
-- Christophe Pettus
   xof@thebuild.com

On 11/5/20 7:45 AM, Tony Shelver wrote:
> 
> 
> ---------- Forwarded message ---------
> From: *Tony Shelver* <tshelver@gmail.com <mailto:tshelver@gmail.com>>
> Date: Thu, 5 Nov 2020 at 17:45
> Subject: Re: JSONB order?
> To: Christophe Pettus <xof@thebuild.com <mailto:xof@thebuild.com>>
> 
> 
> Thanks Christophe, that's what I thought.
> Just seemed weird that they were 'disordered' in exactly the same way 
> every time.

Probably because that resolves to the most efficient way to store in 
jsonb for that particular record.

> 
> FYI, as of Python 3.7, dicts /_are_/ ordered.

By insertion order so updating a dict with a new item will add new key 
to end.

> 
> The problem is that we are possibly going to have many versions of these 
> forms with slightly differing keys, which will be a pain to order in 
> some hard coded way.
> 
> 
> 
> On Thu, 5 Nov 2020 at 17:40, Christophe Pettus <xof@thebuild.com 
> <mailto:xof@thebuild.com>> wrote:
> 
> 
> 
>      > On Nov 5, 2020, at 07:34, Tony Shelver <tshelver@gmail.com
>     <mailto:tshelver@gmail.com>> wrote:
>      > But...  seen above, the order gets mixed up.
>      >
>      > Any ideas?
> 
>     JSON objects, like Python dicts, are not automatically ordered by
>     key.  Once you move from the column space to the JSON object space,
>     you can't rely on the object keys being in a consistent order.
> 
>     You'll want to have a step when ingesting the JSON object into a
>     report that lines up the key values appropriately with the right
>     presentation in the report.
>     --
>     -- Christophe Pettus
>     xof@thebuild.com <mailto:xof@thebuild.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com