Combine two JSON Objects in sql

Question

I am having a scenario where in I have to combine two JSON objects from sql tables and from a single json object. For example if, JSON objects returned from sql query are {"abc":"value1", "xyz":"value2"}, {"pqr":"value3"}

I want to combine these two objects to build using sql query only, {"abc":"value1", "xyz":"value2", "pqr":"value3"}

I thought may be using string function, by treating json object as string.

Could this solve your issue? stackoverflow.com/questions/48911530/… — Pauldb
– Pauldb, Commented Oct 26, 2022 at 6:06

Masklinn · Accepted Answer · 2022-10-26 06:13:43Z

1

While SQL 2016 added support for JSON, I don't think it specifies way to merge or update JSON (only create and query). This means you will need to use DB-specific features, which requires knowing what your database is.

Postgresql has the || operator which will do the job directly:

# select '{"abc":"value1", "xyz":"value2"}'::jsonb || '{"pqr":"value3"}'::jsonb;
                      ?column?                       
-----------------------------------------------------
 {"abc": "value1", "pqr": "value3", "xyz": "value2"}
(1 row)

In other databases, you'll probably have to compose with lower-level utilities (json_set/json_modify) and manual manipulation.

SQLite also has json_patch which would do the job here, however it's a much more powerful and flexible utility based around RFC-7396 JSON Patching, so if the "patcher" is user-controlled it's probably not a good idea.

answered Oct 26, 2022 at 6:13

Masklinn

43.7k4 gold badges58 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shadaab farhan Over a year ago

result of the above suggested method is {"abc":"value1", "xyz": "value2"}{"pqr":"value3"} not {"abc":"value1", "xyz": "value2", "pqr":"value3"}

Masklinn Over a year ago

No it's not? I literally posted a psql dump which shows it results in {"abc": "value1", "pqr": "value3", "xyz": "value2"}. You need to convert the values to JSONB to merge them, not do a string concatenation.

Collectives™ on Stack Overflow

Combine two JSON Objects in sql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related