4 ms·
Can't wait to use the new JSONB operators of 9.5, e.g.: SELECT '{"k1":"v1"}'::JSONB || '{"k1":"v2","k2":true}'::JSONB => {"k1": "v2", "k2": true} In 9.4
by bwblabs 11y ago
Can't wait to use the new JSONB operators of 9.5, e.g.:
SELECT '{"k1":"v1"}'::JSONB || '{"k1":"v2","k2":true}'::JSONB
=> {"k1": "v2", "k2": true}
In 9.4 this is the 'best way' I know:
SELECT
('{' || STRING_AGG(
'"' || COALESCE(j2.key, j1.key) ||
'": ' || TO_JSON(COALESCE(j2.value, j1.value)
), ',') || '}')::JSONB
FROM JSONB_EACH('{"k1":"v1"}') j1
FULL OUTER JOIN
(SELECT * FROM JSONB_EACH('{"k1":"v2","k2":true}')) j2 ON j1.key = j2.key
- andrewgleave 11y agoThe JSONB 9.5 operators have been backported to 9.4 and are available here: https://github.com/erthalion/jsonbx https://github.com/erthalion/jsonbx
- d_luaz 11y agoInteresting to see excitement over new features which I am not aware of. I would like to encourage more people to share on StackBus (FYI, I built it) on why they use PostgreSQL and what they use it for, to provide more insights to those who are evaluating PostgreSQL. http://www.stackbus.com/ http://www.stackbus.com/