copy json to sql table

Question

copy json to sql table

Andrés Espitia 0

Hello Best regards

I have a matrix JON but without column names, I would like to pass it to a SQL table already created with the help of data factory but it generates a conflict because I do not have the column names, I have tried and I have not been able to.

thank you

{
    "dataResult": [
        [
            "f55f8aac-493f-4e3b-8239-d53a838b391e",
            "2023-11-12T00:00:00",
            "f55f8aac-493f-4e3b-8239-d53a838b391e",
            "f55f8aac-493f-4e3b-8239-d53a838b391e_2eed6c90-151c-4cad-8e63-50a8c0714907",
            4,
            "2023-11-12T15:33:04.3645114",
            false,
            "******@abc.com",
            "15",
            "33",
            4,
            "ByotInUcap",
            "DirectRouting",
            "00",
            "",
            "2023-11-12T15:46:43.3826511",
            "2023-11-12T15:33:00.0837744",
            0,
            0,
            "",
            0.0,
            "",
            null,
            "",
            false,
            null,
            5.2481689,
            4.0,
            0.0,
            0.06666666666666667,
            0.06666666666666667,
            1
        ],
        [
            "2291fb62-005a-4260-9625-c5ce68028de0",
            "2023-11-12T00:00:00",
            "2291fb62-005a-4260-9625-c5ce68028de0",
            "2291fb62-005a-4260-9625-c5ce68028de0_fe653722-f2d8-4e9d-bb89-632901b73bce",
            105,
            "2023-11-12T13:59:29.1754",
            true,
            "",
            "13",
            "57",
            0,
            "",
            "",
            "44",
            "******@abc.com",
            "2023-11-12T14:16:25.2208357",
            "2023-11-12T13:57:44.1047622",
            0,
            0,
            "",
            0.0,
            "",
            null,
            "",
            false,
            null,
            106.4068183,
            105.0,
            0.0,
            0.0,
            1.75,
            1
        ],
        [
            "007ce720-df8a-4e06-b1dd-ecc601020e03",
            "2023-11-12T00:00:00",
            "007ce720-df8a-4e06-b1dd-ecc601020e03",
            "007ce720-df8a-4e06-b1dd-ecc601020e03_e5cad9f6-1881-4ea6-8de4-7598633c5632",
            2,
            "2023-11-12T15:20:13.2943648",
            true,
            "",
            "15",
            "20",
            0,
            "",
            "",
            "10",
            "******@abc.com",
            "2023-11-12T15:37:12.1794548",
            "2023-11-12T15:20:10.6953189",
            0,
            0,
            "",
            0.0,
            "",
            null,
            "",
            false,
            null,
            3.5994932,
            2.0,
            0.0,
            0.0,
            0.03333333333333333,
            1
        	]

Anonymous

2023-12-15T01:41:08.0966667+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-12-28T07:01:49.1666667+00:00

Hi Andrés Espitia ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-01-08T08:30:29.1633333+00:00

Hi Andrés Espitia ,

If the below response answered your query, do click Accept Answer and Yes for was this answer helpful. Thankyou

2 answers

Your answer

Anonymous

2023-12-15T01:41:08.0966667+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-12-28T07:01:49.1666667+00:00

Hi Andrés Espitia ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-01-08T08:30:29.1633333+00:00

Hi Andrés Espitia ,

If the below response answered your query, do click Accept Answer and Yes for was this answer helpful. Thankyou

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi Andrés Espitia ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As per my understanding you are trying to load the Json data into SQL table, however, the challenge here is that the Json doesn't have any column names.

You can leverage dataflow to achieve your requirement. However, it will be little lengthy process since data transformation is required.

You can use two 'Flatten transformation' consecutively to flatten the records of 'dataResult' array. Make sure in source you have selected 'Array of documents' as the Json settings.
User's image

User's image

Adding another flatten transformation will convert the array values into individual rows:
User's image

Once all the row values are flattened you can apply logic using derived column transformation to divide it into set of rows and columns.

To achieve this, kindly watch out the below video: How to Copy Complex multi-array JSON into tabular format using mapping dataflow

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou.

Andrés Espitia 0 Reputation points

2023-12-21T15:19:08.5466667+00:00

Hello @AnnuKumari-MSFT , thank you very much for your help

I have a question, in 1columns I capture the data of the number of records per file (the amount varies in each file) I have not been able to pass it automatically through a variable to the dummy expression, can you help me, thank you
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-12-26T08:26:46.7066667+00:00

Hi Andrés Espitia ,

Kindly accept the answer by clicking on Accept answer button. Thankyou

Answer 2

Here's is an example SQL that will parse the nested json arrays into rows and columns using OPENJSON and PIVOT that may help with your task.

DECLARE @json varchar(MAX) = '{
    "dataResult": [
        [
            "f55f8aac-493f-4e3b-8239-d53a838b391e",
            "2023-11-12T00:00:00",
            "f55f8aac-493f-4e3b-8239-d53a838b391e",
            "f55f8aac-493f-4e3b-8239-d53a838b391e_2eed6c90-151c-4cad-8e63-50a8c0714907",
            4,
            "2023-11-12T15:33:04.3645114",
            false,
            "******@abc.com",
            "15",
            "33",
            4,
            "ByotInUcap",
            "DirectRouting",
            "00",
            "",
            "2023-11-12T15:46:43.3826511",
            "2023-11-12T15:33:00.0837744",
            0,
            0,
            "",
            0.0,
            "",
            null,
            "",
            false,
            null,
            5.2481689,
            4.0,
            0.0,
            0.06666666666666667,
            0.06666666666666667,
            1
        ],
        [
            "2291fb62-005a-4260-9625-c5ce68028de0",
            "2023-11-12T00:00:00",
            "2291fb62-005a-4260-9625-c5ce68028de0",
            "2291fb62-005a-4260-9625-c5ce68028de0_fe653722-f2d8-4e9d-bb89-632901b73bce",
            105,
            "2023-11-12T13:59:29.1754",
            true,
            "",
            "13",
            "57",
            0,
            "",
            "",
            "44",
            "******@abc.com",
            "2023-11-12T14:16:25.2208357",
            "2023-11-12T13:57:44.1047622",
            0,
            0,
            "",
            0.0,
            "",
            null,
            "",
            false,
            null,
            106.4068183,
            105.0,
            0.0,
            0.0,
            1.75,
            1
        ],
        [
            "007ce720-df8a-4e06-b1dd-ecc601020e03",
            "2023-11-12T00:00:00",
            "007ce720-df8a-4e06-b1dd-ecc601020e03",
            "007ce720-df8a-4e06-b1dd-ecc601020e03_e5cad9f6-1881-4ea6-8de4-7598633c5632",
            2,
            "2023-11-12T15:20:13.2943648",
            true,
            "",
            "15",
            "20",
            0,
            "",
            "",
            "10",
            "******@abc.com",
            "2023-11-12T15:37:12.1794548",
            "2023-11-12T15:20:10.6953189",
            0,
            0,
            "",
            0.0,
            "",
            null,
            "",
            false,
            null,
            3.5994932,
            2.0,
            0.0,
            0.0,
            0.03333333333333333,
            1
        	]		
		]
}';

SELECT
	 [0]  AS col0
	,[1]  AS col1
	,[2]  AS col2
	,[3]  AS col3
	,[4]  AS col4
	,[5]  AS col5
	,[6]  AS col6
	,[7]  AS col7
	,[8]  AS col8
	,[9]  AS col9
	,[10] AS col10
	,[11] AS col11
	,[12] AS col12
	,[13] AS col13
	,[14] AS col14
	,[15] AS col15
	,[16] AS col16
	,[17] AS col17
	,[18] AS col18
	,[19] AS col19
	,[20] AS col20
	,[21] AS col21
	,[22] AS col22
	,[23] AS col23
	,[24] AS col24
	,[25] AS col25
	,[26] AS col26
	,[27] AS col27
	,[28] AS col82
	,[29] AS col29
	,[30] AS col30
	,[31] AS col31
FROM (
	SELECT
		  col_value.[key] AS col_num
		, col_value.value AS col_val
	FROM OPENJSON(@json, '$.dataResult') AS row_value
	CROSS APPLY OPENJSON(row_value.value) AS col_value
    ) AS source
PIVOT 
(
	MAX(source.col_val)
	FOR source.[col_num] IN( [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] )
) AS pivoted_data;

Share via

copy json to sql table

2 answers

Your answer