Get raw JSON of object when using OpenJSON

Question

This is an awkward thing to word, so if this has been asked before sorry, I couldn't find any answers.

I'm taking JSON which is a list of objects and storing the fields in a table, however the JSON is from a third party and is prone to adding in new fields, so I'd like to also save the raw JSON of the object. So that if any unhandled fields were added we have the raw JSON, and can update the DB for those fields and run through the stored JSON, so no data is lost.

I can't however find or figure out how to just get the raw JSON of the current list item.

Basic OPENJSON I have:

DECLARE @JSON NVARCHAR(MAX) = '{"data": [
        {   "id": 1,
            "name": "foo",
            "type": "bar",
            "random": "Potato"},
        {   "id": 2,
            "name": "cake",
            "type": "special",
            "random": "unhandled field"}]}'

SELECT *
FROM OPENJSON(@JSON, '$.data')
      WITH (
        [id] INT '$.id',
        [type] NVARCHAR(50) '$.type',
        [name] NVARCHAR(50) '$.name'
      ) jsonData

but I can't find anywhere documenting how to also as a field get the entire JSON for the item.

My ideal tabled output being:

id	name	type	json
1	foo	bar	{ "id": 1,"name": "foo", "type": "bar", "random": "Potato"}
2	cake	special	{ "id": 2, "name": "cake", "type": "special", "random": "unhandled field"}

@JeroenMostert wonderful, I'd tried just '$' but didn't know I'd need the "AS JSON" — Vereonix
– Vereonix, Commented Sep 20, 2022 at 11:07

Jáder Carvalho de Medeiros · Accepted Answer · 2024-02-28 14:16:07Z

1

DECLARE @JSON NVARCHAR(MAX) = '{"data": [
        {   "id": 1,
            "name": "foo",
            "type": "bar",
            "random": "Potato"},
        {   "id": 2,
            "name": "cake",
            "type": "special",
            "random": "unhandled field"}]}'

SELECT *
FROM OPENJSON(@JSON, '$.data')
              WITH (
                  [id] INT '$.id',
                  [type] NVARCHAR(50) '$.type',
                  [name] NVARCHAR(50) '$.name',
                  [json] NVARCHAR(MAX) '$' AS JSON
                  ) jsonData

answered Feb 28, 2024 at 14:16

Jáder Carvalho de Medeiros

1933 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

Get raw JSON of object when using OpenJSON

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related