SQL JSON - Append Json to Json

Question

I Have this json:

{"keyvalue": {"head": {"id": ""},"column": {"id": ""},"degrees": {"id": ""}}}

How do i add this json to the key "keyvalue":

"somekey": { "id" : "" }

so my json looks like this:

{"keyvalue": {"head": {"id": ""},"column": {"id": ""},"degrees": {"id": ""}, "somekey": { "id" : "" }}}

i tried this:

SELECT JSON_MODIFY('{"keyvalue": {"head": {"id": ""},"column": {"id": ""},"degrees": {"id": ""}}}', 'append $', json_query(N' {"somekey": {"id" : ""}}'))
FROM PL_Table
WHERE PL_Id = 6;

but nothing changed

UPDATE

i have this now:

update PL_PageLayout
set PL_Json = json_modify('{

"keyvalue": {
    "obj1": {
        "id": ""
    },
    "obj2": {
        "id": ""
    },
    "obj3": {
        "id": ""
    }
}


}', 'append $.keyvalue.content', '{"id" : "ddd"}')
FROM PL_PageLayout
WHERE PL_Id = 6;

Output is:

{"keyvalue": {"obj1": {"id": ""},"obj2": {"id": ""},"obj3": {"id": ""},"content":["{\"id\" : \"ddd\"}"]}}

but the

"content":["{\"id\" : \"ddd\"}"]

needs to be

"content":{\"id\" : \"ddd\"}

Are you trying to update rows in PL_Table? A SELECT query won't do that. You have to use an UPDATE. — David Browne - Microsoft
– David Browne - Microsoft, Commented Dec 8, 2019 at 17:19
im just using select to see the results, ill use update later — redoc01
– redoc01, Commented Dec 8, 2019 at 17:23
i see what you mean David Browne i have something working now — redoc01
– redoc01, Commented Dec 8, 2019 at 17:25

Zhorov · Accepted Answer · 2019-12-08 18:04:06Z

1

The reason for this result is that with append optional modifier, the new value is appended to the array referenced by the path. You also need to use JSON_QUERY() to get a properly formatted JSON, because JSON_MODIFY escapes all special characters in the new value if the type of the value is varchar or nvarchar.

You may try with the following approach, without using append:

DECLARE @json nvarchar(max) = N'{"keyvalue": {"head": {"id": ""},"column": {"id": ""},"degrees": {"id": ""}}}'

SELECT JSON_MODIFY(
   @json,
   '$.keyvalue.somekey',
   JSON_QUERY(N'{"id" : ""}')
)

Result:

{"keyvalue": {"head": {"id": ""},"column": {"id": ""},"degrees": {"id": ""},"somekey":{"id" : ""}}}

edited Dec 8, 2019 at 18:04

answered Dec 8, 2019 at 17:44

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

2 Comments

redoc01 Over a year ago

that result is close the output is: { "keyvalue": { "obj1": { "id": "" }, "obj2": { "id": "" }, "obj3": { "id": "" } ,"content":"{\"id\" : \"ddd\"}"} } but the quotes i dont need why are they there?

Zhorov Over a year ago

@redoc01 Use JSON_QUERY() to prevent escaping.

Collectives™ on Stack Overflow

SQL JSON - Append Json to Json

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related