SQL Server How to insert JSON to an existing table without specifying column names?

brc7rcf0 于 2023-02-18 发布在其他

关注(0)|答案(2)|浏览(162)

To a table with two columns, named Id and Name , how can I insert the following json as rows, but without explicitly specifying the column names of the table (i.e. without WITH (Id ..., Name ... )?

[
    {
        "Id": "f08af9c2-8e67-4a7f-9413-1afffa2de96b",
        "SomeOtherKey": " ... ",
        "Name": "The One",
        ...
    },
    {
        "Name": "All is one"
        "Id": "9bbb094b-aa64-4c36-90a2-50e10f91c6a3",
        "Whatever": 99,
        ...
    },

    {       
        "Id": "af9d22d8-1e46-4d57-8179-75f094d2efa1",
        "SomeArrayWhyNot": [0, 1, 1, 2, 3, 5, 8, 13, 21]
        "Surprise": "This one does not have a Name value!!! 😱"
        ...
    },

    ...
]

The question is basically how to make SQL match the key-name to its suitable column name, ignoring json values with keys that do not have suitable column names, resulting with the following table (for the above json example):

Id	Name
f08af9c2-8e67-4a7f-9413-1afffa2de96b	The One
9bbb094b-aa64-4c36-90a2-50e10f91c6a3	All is one
af9d22d8-1e46-4d57-8179-75f094d2efa1	`NULL`
...	...

sql-server

来源：https://stackoverflow.com/questions/75476354/how-to-insert-json-to-an-existing-table-without-specifying-column-names

2条答案

按热度按时间

4dbbbstv1#

You can use the OPENJSON function in SQL Server to parse the JSON data and then use INSERT INTO to insert the data into the table.

INSERT INTO YourTable (Id, Name)
SELECT *
FROM OPENJSON('[
    {
        "Id": "f08af9c2-8e67-4a7f-9413-1afffa2de96b",
        "SomeOtherKey": " ... ",
        "Name": "The One"
    },
    {
        "Name": "All is one",
        "Id": "9bbb094b-aa64-4c36-90a2-50e10f91c6a3",
        "Whatever": 99
    },
    {       
        "Id": "af9d22d8-1e46-4d57-8179-75f094d2efa1",
        "SomeArrayWhyNot": [0, 1, 1, 2, 3, 5, 8, 13, 21],
        "Surprise": "This one doesn\'t have a Name value!!! 😱"
    }
]') WITH (Id nvarchar(50) '$.Id', Name nvarchar(50) '$.Name')

赞(0）回复(0）举报 2023-02-18

72qzrwbm2#

Not sure why you want this, but you can also do this:

INSERT INTO YourTable (Id, Name)
SELECT JSON_VALUE(x.value, '$.Id'), JSON_VALUE(x.value, '$.Name')
FROM OPENJSON('[{
        "Id": "f08af9c2-8e67-4a7f-9413-1afffa2de96b",
        "SomeOtherKey": " ... ",
        "Name": "The One"
    },
    {
        "Name": "All is one",
        "Id": "9bbb094b-aa64-4c36-90a2-50e10f91c6a3",
        "Whatever": 99
    },
    {       
        "Id": "af9d22d8-1e46-4d57-8179-75f094d2efa1",
        "SomeArrayWhyNot": [0, 1, 1, 2, 3, 5, 8, 13, 21],
        "Surprise": "This one doesn''t have a Name value!!! 😱"
    }]') x

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server How to insert JSON to an existing table without specifying column names?

2条答案

相关问题

热门标签

最新问答