jsonb_set example

jsonb_set.md

What is the result of this?

SELECT jsonb_set('{}', '{test}', to_jsonb((SELECT 'test' LIMIT 0)::text));

---

SELECT to_jsonb((SELECT 'test' LIMIT 0)::text);

Returns null.

---

SELECT to_jsonb(null::text);

Also returns null.

---

SELECT jsonb_set('{}', '{test}', null);

Also, Mother Nature, returns null, even when we get not-null document and trying to add null there.

---

The correct null should be added like this:

SELECT jsonb_set('{}', '{test}', 'null');

Here you get the expected {"test": null}.

---

Here is the complete example which adds null to the document or a value depending of the result of the nested query.

SELECT jsonb_set('{}', '{test}', COALESCE(to_jsonb((SELECT 'test' LIMIT 0)::text), 'null'));