From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Gianni Ceccarelli <dakkar(at)thenautilus(dot)net> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Why does jsonb_set() remove non-mentioned keys? |
Date: | 2019-07-04 21:09:14 |
Message-ID: | CAKFQuwbb7=ciX-MXeDyAk1H4WFNQZXpQh82eby8ExY4epYT0gQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thursday, July 4, 2019, Gianni Ceccarelli <dakkar(at)thenautilus(dot)net> wrote:
> Some experimentation:
>
> > \pset null '((null))'
>
> > select jsonb_set('{"foo":1}'::jsonb,'{bar}','null'::jsonb,true);
> ┌─────────────────────────┐
> │ jsonb_set │
> ├─────────────────────────┤
> │ {"bar": null, "foo": 1} │
> └─────────────────────────┘
No SQL null, ok
>
>
> > select jsonb_set('{"foo":1}'::jsonb,'{bar}',to_jsonb(null::text),true);
> ┌───────────┐
> │ jsonb_set │
> ├───────────┤
> │ ((null)) │
> └───────────┘
Sql null poisons the expression and so sql null is the result
>
> That's a bit weird already. Also:
>
> > select null::jsonb;
> ┌──────────┐
> │ jsonb │
> ├──────────┤
> │ ((null)) │
> └──────────┘
>
>
Sql null
> > select 'null'::jsonb;
> ┌───────┐
> │ jsonb │
> ├───────┤
> │ null │
> └───────┘
>
>
Json null
> > select to_jsonb(null::int);
> ┌──────────┐
> │ to_jsonb │
> ├──────────┤
> │ ((null)) │
> └──────────┘
>
>
Sql null poisons the function call which immediately returns sql null
> > select to_jsonb('null'::text);
> ┌──────────┐
> │ to_jsonb │
> ├──────────┤
> │ "null" │
> └──────────┘
>
>
Json null
> I'm sharing Thomas's confusion…
>
>
Sql null and json null are represented differently; strict functions with
sql null inputs yield sql null output without even executing the function
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-07-04 21:20:17 | Re: Converting to identity columns with domains on PK columns |
Previous Message | Gianni Ceccarelli | 2019-07-04 21:03:50 | Re: Why does jsonb_set() remove non-mentioned keys? |