Trying to insert a an attribute after a JSON array using SQL

Question

(If my terms are wrong, please forgive me, I'm used to working with XML) Good morning. I am trying to insert an attribute after a JSON array, but I haven't for the key that turns that lock. This is the JSON that I am working with:

{
    "extension": [
        {
            "url": "string1",
            "valueCoding": [
                {
                    "att1": "value1",
                    "att2": "value2",
                    "att3": "value3"
                }
            ]
        },
        {
            "url": "string1",
            "valueCoding": [
                {
                    "att1": "value1",
                    "att2": "value4",
                    "att3": "value5"
                }
            ]
        }
    ]
}

And what I'm trying to get is:

{
    "extension": [
        {
            "url": "string1",
            "valueCoding": [
                {
                    "att1": "value1",
                    "att2": "value2",
                    "att3": "value3"
                }
            ]
        },
        {
            "url": "string1",
            "valueCoding": [
                {
                    "att1": "value1",
                    "att2": "value4",
                    "att3": "value5"
                }
            ]
        }
    ]
    "url" : "http://someurl.org"
}

I'm able to get it imported as another array using this:

DECLARE @someUrl NVARCHAR(MAX) = N'{ "url" : "http://someurl.org"}'
SELECT (SELECT JSON_QUERY(extension) AS extension FROM @Extension2) AS extension, JSON_QUERY(@raceUrl) as url FROM @Extension2  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 

but it adds it as an array rather than an attribute.

{
    "extension": [
        {
                "url": "string1",
                "valueCoding": [
                    {
                        "att1": "value1",
                        "att2": "value2",
                        "att3": "value3"
                    }
            ]
        },
        {
                "url": "string1",
                "valueCoding": [
                    {
                        "att1": "value1",
                        "att2": "value4",
                        "att3": "value5"
                    }
            ]
        }
    ],
    "url": {
        "url": "http://someurl.org"
    }
}

Any assistance or direction would be much appreciated

Answer 1

I am not familiar with the syntax, but can you try this?

DECLARE @someUrl NVARCHAR(MAX) = N'"http://someurl.org"'
SELECT (SELECT JSON_QUERY(extension) AS extension FROM @Extension2) AS extension, JSON_QUERY(@someUrl) as url FROM @Extension2  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 

And I assume, there is a typo in your query: JSON_QUERY(@raceUrl) -> should be JSON_QUERY(@someUrl)?