0

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.

shadaab farhan
  • 3
  • 1
  • Could this solve your issue? https://stackoverflow.com/questions/48911530/concatenate-or-merge-two-json-objects-in-sql-server – Pauldb Oct 26 '22 at 06:06

1 Answers1

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.

Masklinn
  • 34,759
  • 3
  • 38
  • 57
  • result of the above suggested method is {"abc":"value1", "xyz": "value2"}{"pqr":"value3"} not {"abc":"value1", "xyz": "value2", "pqr":"value3"} – shadaab farhan Oct 26 '22 at 09:01
  • 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. – Masklinn Oct 26 '22 at 09:54