json file in sql server

Question

could you please explain how we can load below curly bracket files in sql server when there are multiple unwanted curly brackets are there apart from main data?

in this case data is available in till Result only

{
    "Generic": {
        "id": "33",
        "Products": [
            {
                "Code": "111",
                "Amount": 1.0,
                "category": "33",
                "price": 11,
                "totalprice": 233
            }
        ],
        "Result": "test",
    },
    "Notification": {
        "Environment": "local",
        "Instance": "local",
        "Time": "00"}

Answer 1

Hi @Vineet S,

Please try the following solution.

-- INSERT INTO tableName -- uncomment when you are ready
SELECT j1.id, j1.Result, prod.*
FROM OPENROWSET(BULK 'e:\temp\vineet.json', SINGLE_CLOB) AS j
    CROSS APPLY
    OPENJSON(BulkColumn,'$.Generic')
    WITH
    (
        id			INT '$.id'
      , Result		NVARCHAR(30) '$.Result'
      , Products	NVARCHAR(max) AS JSON
    ) AS j1
CROSS APPLY (
    SELECT *
    FROM OPENJSON(j1.Products)
    WITH
    (
		Code		VARCHAR(50)		'$.Code',
		Amount		DECIMAL(10,2)	'$.Amount',
		category	INT				'$.category',
		price		DECIMAL(10,2)	'$.price',
		totalprice	DECIMAL(10,2)	'$.totalprice'
    ) j2
) AS prod;