none
import JSON into SQL server table RRS feed

  • Question

  • How do I import this JSON into SQL server table format

    [
        {
            "id": 13,
            "name": "starters",
            "sub_category": [
                {
                    "id": 11,
                    "sub_category_name": "Chinese",
                    "product_list": [
                        {
                            "id": 1,
                            "name": "Crispy Chilli Baby Corn",
                            "description": "1 Plate Crispy Chilli Baby Corn",
                            "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                            "price": 220,
                            "tax": 5,
                            "is_active": false,
                            "quantity": 0,
                            "addons": []
                        },
                        {
                            "id": 2,
                            "name": "Crunchy Potatos",
                            "description": "Crunchy Potatos",
                            "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                            "price": 190,
                            "tax": 5,
                            "is_active": true,
                            "quantity": 0,
                            "addons": [
                                {
                                    "id": 2,
                                    "name": "Raita",
                                    "description": "Mixed Raita",
                                    "price": 80,
                                    "tax": 5,
                                    "is_active": true,
                                    "quantity": 0,
                                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/no_image.jpg"
                                }
                            ]
                        },
                        {
                            "id": 12,
                            "name": "Vegetable Manchurian ",
                            "description": "1 plate Vegetable Manchurian ",
                            "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                            "price": 200,
                            "tax": 5,
                            "is_active": true,
                            "quantity": 0,
                            "addons": [
                                {
                                    "id": 1,
                                    "name": "Raita",
                                    "description": "Mixed Raita",
                                    "price": 80,
                                    "tax": 5,
                                    "is_active": true,
                                    "quantity": 0,
                                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/no_image.jpg"
                                }
                            ]
                        },
                        {
                            "id": 13,
                            "name": "Saute 'Veg' in Sweet Chilli Sauce",
                            "description": "Saute 'Veg' in Sweet Chilli Sauce",
                            "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                            "price": 300,
                            "tax": 5,
                            "is_active": true,
                            "quantity": 0,
                            "addons": []
                        },
                        {
                            "id": 29,
                            "name": "Egg Chicken",
                            "description": "Egg Chicken",
                            "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                            "price": 200,
                            "tax": 5,
                            "is_active": true,
                            "quantity": 0,
                            "addons": []
                        }
                    ]
                },
                {
                    "id": 12,
                    "sub_category_name": "Indian",
                    "product_list": [
                        {
                            "id": 3,
                            "name": "Fish Fry",
                            "description": "Fish Fry(2 pcs)",
                            "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                            "price": 240,
                            "tax": 5,
                            "is_active": false,
                            "quantity": 0,
                            "addons": []
                        },
                        {
                            "id": 4,
                            "name": "Fish Finger",
                            "description": "Fish Finger(8 pcs)",
                            "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                            "price": 240,
                            "tax": 5,
                            "is_active": true,
                            "quantity": 0,
                            "addons": []
                        },
                        {
                            "id": 18,
                            "name": "Thai Chicken",
                            "description": "Thai Chicken (8 Pcs)",
                            "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                            "price": 230,
                            "tax": 5,
                            "is_active": true,
                            "quantity": 0,
                            "addons": []
                        }
                    ]
                }
            ],
            "product_list": []
        },
        {
            "id": 14,
            "name": "main course",
            "sub_category": [],
            "product_list": [
                {
                    "id": 8,
                    "name": "Paneer Butter Masala ",
                    "description": "Paneer Butter Masala (10 pcs)",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 220,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                },
                {
                    "id": 9,
                    "name": "Kadhai Paneer ",
                    "description": "Kadhai Paneer (8 pcs) ",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 220,
                    "tax": 20,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                }
            ]
        },
        {
            "id": 15,
            "name": "noodles",
            "sub_category": [],
            "product_list": [
                {
                    "id": 24,
                    "name": "Chicken Hakka Noodles",
                    "description": "Chicken Hakka Noodles",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 200,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                }
            ]
        },
        {
            "id": 16,
            "name": "beverage",
            "sub_category": [],
            "product_list": [
                {
                    "id": 19,
                    "name": "Saute Vegatables",
                    "description": "Saute Vegatables",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 220,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                },
                {
                    "id": 31,
                    "name": "Jaljeera",
                    "description": "Jaljeera",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 30,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                },
                {
                    "id": 32,
                    "name": "Fresh Lime Soda",
                    "description": "Fresh Lime Soda",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 40,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                }
            ]
        },
        {
            "id": 17,
            "name": "desserts",
            "sub_category": [],
            "product_list": [
                {
                    "id": 33,
                    "name": "Hot Gulab Jamum",
                    "description": "Hot Gulab Jamum(2 Pcs)",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 30,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                },
                {
                    "id": 36,
                    "name": "Firni ",
                    "description": "Firni (2 Pcs)",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 70,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                }
            ]
        },
        {
            "id": 18,
            "name": "crunches",
            "sub_category": [],
            "product_list": [
                {
                    "id": 23,
                    "name": "Butter Paneer Masala ",
                    "description": "Butter Paneer Masala (8 Pcs) ",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 200,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                },
                {
                    "id": 26,
                    "name": "Chicken 65",
                    "description": "Chicken 65",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 230,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                },
                {
                    "id": 27,
                    "name": "Kung pao Chicken ",
                    "description": "Kung pao Chicken ",
                    "image": "http://dev.maxmobility.in:81/grabpos-web/dev/public/uploads/menu_item/normal/46761564643937.jpg",
                    "price": 300,
                    "tax": 5,
                    "is_active": true,
                    "quantity": 0,
                    "addons": []
                }
            ]
        }
    ]


    Thanks in advance.

    Thursday, December 26, 2019 9:40 AM

All replies

  • To start with, how do your tables look like?

    Next, which version of SQL Server are you on? Starting with SQL 2016 you can use OPENJSON to easily shred the JSON text inside SQL Server. If you are on an older version, you will need to crack the text client-side and send it in tabular format to SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 26, 2019 11:15 AM
  • I am using SQL server 2016

    Below query works if "addons" is in jason format. But we also need to store data in table if "addons" is not in jason format.

    select
        Category.Id as CategoryID, Category.name as CategoryName
        ,sub_category.Id as sub_category_Id, sub_category.sub_category_name, product_list.id as product_list_id,
    product_list.name as product_list_name,
    product_list.description as product_list_description,product_list.image as product_list_image,product_list.price as product_list_price,
    product_list.tax as product_list_tax,product_list.is_active as product_list_is_active,product_list.quantity as product_list_quantity,
    ADDOns.*  
    from   openjson (@json)
    with
    (
        id bigint,
        name nvarchar(100),
        sub_category nvarchar(max) as json
    )
    as Category
    cross apply openjson (Category.sub_category)
    with
    (
        Id  bigint,
        sub_category_name nvarchar(100),
        product_list nvarchar(max) as json
    ) as sub_category
    cross apply openjson (sub_category.product_list)
    with
    (
        id bigint,
        name nvarchar(100) '$.Name',
        description nvarchar(100),
        image nvarchar(100),
        price decimal(18,2),
        tax int,
        is_active bit,
        quantity int,
        addons nvarchar(max) as json
    )
    as product_list
    cross apply openjson (product_list.ADDOns)
    with
    (
        id bigint,
        name nvarchar(100) ,
        description nvarchar(100),
        price decimal(18,2),
        tax int,
        is_active bit,
        quantity int,
        image nvarchar(100)
    )
    as ADDOns

    Please help.

    Thursday, December 26, 2019 11:54 AM
  • Below query works if "addons" is in jason format. But we also need to store data in table if "addons" is not in jason format.

    So first you ask how to shred JSON data into SQL Server table, whereupon you post the code for that. And now you are asking how handle data that is not JSON?

    In your sample JSON, addons is in most cases an empty array, and in the one example where it is non-empty, it looks like valid JSON to me.

    It is a little difficult to help when you don't even show example of what you want help with. But I guess that if you have a document that is not valid JSON, it is not going to be an easy ride.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 26, 2019 1:03 PM
  • Dear Erland,

    The above query return only 2 rows where id = 13 and addons is not empty. But I need all rows if nested jason is empty.

    I also send you a mail with jason and query which you can run in SSMS.

    Thanks in advance.

    Friday, December 27, 2019 6:08 AM
  • The above query return only 2 rows where id = 13 and addons is not empty. But I need all rows if nested jason is empty.

    There is a simple fix. Change

    cross apply openjson (product_list.addons)

    to

    OUTER apply openjson (product_list.addons)

    The APPLY operator is akin to a join, but in difference to JOIN, you can use references from the left side in the table expression on the right side.

    CROSS APPLY is like an inner join. That is, you lose the row on the left side, if the expression on the right side does not yield any rows. With OUTER APPLY all rows in the left side are always retained, just like in a LEFT OUTER JOIN.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 27, 2019 10:45 PM