sql server column mapping

Question

Hey,

I got less in json files but there are more columns target table in sql server how to map if receive situation like this

DockerfileCopy

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

Here is the mapping

Code-Codenew,

Amount-Amount_new, category-category_new, "price"-"price","totalprice1"-"totalprice1","totalprice2","totalprice3","totalprice4" and so on

Target table columns are

     "Codenew","Amount_new", "category_new","price","totalprice1","totalprice2","totalprice3","totalprice4"

how to resolve this issue

Answer 1

target table has more columns than source and different names how to map it

here is the mapping for source and target table

Amount-Amount_new, category-category_new, "price"-"price","totalprice"-"totalprice1", Result-"totalprice2" ....... reaminginare extra columns in target table ,"totalprice3","totalprice4" and so on

You can use INSERT...SELECT to map the json fields to target table columns with different names. Any columns not mapped to the target table will contain NULL.

The below example is based on your example json (with missing ending "}" added). I used data type varchar(100) for illustration but you should change the data types to match the actual target column data types.

If the Products json array contains more than one entry, each row all will have the same value in the result column because that json field is outside the array.

DECLARE @json nvarchar(MAX) = N'

{

"Generic": {

"id": "33",

"Products": [

{

"Code": "111",

"Amount": 1.0,

"category": "33",

"price": 11,

"totalprice": 233

}

],

"Result": "test"

},

"Notification": {

"Environment": "local",

"Instance": "local",

"Time": "00"}

}

';

DROP TABLE IF EXISTS #TargetTable;

CREATE TABLE #TargetTable (

Codenew varchar(100)

, Amount_new varchar(100)

, category_new varchar(100)

, price varchar(100)

, totalprice1 varchar(100)

, totalprice2 varchar(100)

, totalprice3 varchar(100)

, totalprice4 varchar(100)

);

INSERT INTO #TargetTable(

Codenew

, Amount_new

, category_new

, price

, totalprice1

, totalprice2

)

SELECT

Code AS Codenew

, Amount AS Amount_new

, category AS category_new

, price AS price_new

, totalprice AS totalprice1

, result AS totalprice2

FROM OPENJSON(@json)

WITH (

Products nvarchar(MAX) '$.Generic.Products' AS JSON

, result varchar(100) '$.Generic.Result'

)

CROSS APPLY OPENJSON(Products)

WITH (

Code varchar(100)

, Amount varchar(100)

, category varchar(100)

, price varchar(100)

, totalprice varchar(100)

);

SELECT *

FROM #TargetTable;

---

Results:

Codenew	Amount_new	category_new	price	totalprice1	totalprice2	totalprice3	totalprice4
111	1.0	33	11	233	test	NULL	NULL