Revisions to How to parse a JSON with missing property name into a db table

edited body

Source Link

edited Feb 14, 2022 at 13:20

29.6k
6
30
54

Possible options are: 1) Using OPENJSON() twice (with default and explicit schema) and an additional APPLY operator or 2) Using OPENJSON() (with default schema) and JSON_VALUE():

JSON:

DECLARE @json nvarchar(max) = N'{
   "projects":{
      "project1id":{
         "data":{
            "customer":"Cust1",
            "name":"Project Name 1"
         },
         "projectType":"type0"
      },
      "project2id":{
         "data":{
            "customer":"Customer 2",
            "name":"Name 2",
            "projectManager":"Man Ager"
         },
         "projectType":"type2"
      },
      "Project3id":{
         "data":{
            "customer":"Another Customer",
            "name":"Another Project"
         },
         "projectType":"type1"
      }
   }
}'

Statement with OPENJSON() and APPLY operator:

SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
   name nvarchar(max100) '$.data.name',
   customer nvarchar(max100) '$.data.customer',
   projectType nvarchar(max100) '$.projectType',
   projectManager nvarchar(max100) '$.data.projectManager'
) j2

Statement with OPENJSON() and JSON_VALUE():

SELECT 
   projectId = [key],
   name = JSON_VALUE([value], '$.data.name'),
   customer = JSON_VALUE([value], '$.data.customer'),
   projectType = JSON_VALUE([value], '$.projectType'),
   projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')

Possible options are: 1) Using OPENJSON() twice (with default and explicit schema) and an additional APPLY operator or 2) Using OPENJSON() (with default schema) and JSON_VALUE():

JSON:

DECLARE @json nvarchar(max) = N'{
   "projects":{
      "project1id":{
         "data":{
            "customer":"Cust1",
            "name":"Project Name 1"
         },
         "projectType":"type0"
      },
      "project2id":{
         "data":{
            "customer":"Customer 2",
            "name":"Name 2",
            "projectManager":"Man Ager"
         },
         "projectType":"type2"
      },
      "Project3id":{
         "data":{
            "customer":"Another Customer",
            "name":"Another Project"
         },
         "projectType":"type1"
      }
   }
}'

Statement with OPENJSON() and APPLY operator:

SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
   name nvarchar(max) '$.data.name',
   customer nvarchar(max) '$.data.customer',
   projectType nvarchar(max) '$.projectType',
   projectManager nvarchar(max) '$.data.projectManager'
) j2

Statement with OPENJSON() and JSON_VALUE():

SELECT 
   projectId = [key],
   name = JSON_VALUE([value], '$.data.name'),
   customer = JSON_VALUE([value], '$.data.customer'),
   projectType = JSON_VALUE([value], '$.projectType'),
   projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')

Possible options are: 1) Using OPENJSON() twice (with default and explicit schema) and an additional APPLY operator or 2) Using OPENJSON() (with default schema) and JSON_VALUE():

JSON:

DECLARE @json nvarchar(max) = N'{
   "projects":{
      "project1id":{
         "data":{
            "customer":"Cust1",
            "name":"Project Name 1"
         },
         "projectType":"type0"
      },
      "project2id":{
         "data":{
            "customer":"Customer 2",
            "name":"Name 2",
            "projectManager":"Man Ager"
         },
         "projectType":"type2"
      },
      "Project3id":{
         "data":{
            "customer":"Another Customer",
            "name":"Another Project"
         },
         "projectType":"type1"
      }
   }
}'

Statement with OPENJSON() and APPLY operator:

SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
   name nvarchar(100) '$.data.name',
   customer nvarchar(100) '$.data.customer',
   projectType nvarchar(100) '$.projectType',
   projectManager nvarchar(100) '$.data.projectManager'
) j2

Statement with OPENJSON() and JSON_VALUE():

SELECT 
   projectId = [key],
   name = JSON_VALUE([value], '$.data.name'),
   customer = JSON_VALUE([value], '$.data.customer'),
   projectType = JSON_VALUE([value], '$.projectType'),
   projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')

Spelling

Source Link

edited Feb 14, 2022 at 13:07

Zhorov

29.6k
6
30
54

A possible optionPossible options are: 1) Using OPENJSON() twice (with default and explicit schema) and an additional APPLY operator or 2) Using OPENJSON() (with default schema) and JSON_VALUE():

JSON:

DECLARE @json nvarchar(max) = N'{
   "projects":{
      "project1id":{
         "data":{
            "customer":"Cust1",
            "name":"Project Name 1"
         },
         "projectType":"type0"
      },
      "project2id":{
         "data":{
            "customer":"Customer 2",
            "name":"Name 2",
            "projectManager":"Man Ager"
         },
         "projectType":"type2"
      },
      "Project3id":{
         "data":{
            "customer":"Another Customer",
            "name":"Another Project"
         },
         "projectType":"type1"
      }
   }
}'

Statement with OPENJSON() and APPLY operator:

SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
   name nvarchar(max) '$.data.name',
   customer nvarchar(max) '$.data.customer',
   projectType nvarchar(max) '$.projectType',
   projectManager nvarchar(max) '$.data.projectManager'
) j2

Statement with OPENJSON() and JSON_VALUE():

SELECT 
   projectId = [key],
   name = JSON_VALUE([value], '$.data.name'),
   customer = JSON_VALUE([value], '$.data.customer'),
   projectType = JSON_VALUE([value], '$.projectType'),
   projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')

A possible option are: 1) Using OPENJSON() twice and an additional APPLY operator or 2) Using OPENJSON() and JSON_VALUE():

JSON:

DECLARE @json nvarchar(max) = N'{
   "projects":{
      "project1id":{
         "data":{
            "customer":"Cust1",
            "name":"Project Name 1"
         },
         "projectType":"type0"
      },
      "project2id":{
         "data":{
            "customer":"Customer 2",
            "name":"Name 2",
            "projectManager":"Man Ager"
         },
         "projectType":"type2"
      },
      "Project3id":{
         "data":{
            "customer":"Another Customer",
            "name":"Another Project"
         },
         "projectType":"type1"
      }
   }
}'

Statement with OPENJSON() and APPLY operator:

SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
   name nvarchar(max) '$.data.name',
   customer nvarchar(max) '$.data.customer',
   projectType nvarchar(max) '$.projectType',
   projectManager nvarchar(max) '$.data.projectManager'
) j2

Statement with OPENJSON() and JSON_VALUE():

SELECT 
   projectId = [key],
   name = JSON_VALUE([value], '$.data.name'),
   customer = JSON_VALUE([value], '$.data.customer'),
   projectType = JSON_VALUE([value], '$.projectType'),
   projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')

Possible options are: 1) Using OPENJSON() twice (with default and explicit schema) and an additional APPLY operator or 2) Using OPENJSON() (with default schema) and JSON_VALUE():

JSON:

DECLARE @json nvarchar(max) = N'{
   "projects":{
      "project1id":{
         "data":{
            "customer":"Cust1",
            "name":"Project Name 1"
         },
         "projectType":"type0"
      },
      "project2id":{
         "data":{
            "customer":"Customer 2",
            "name":"Name 2",
            "projectManager":"Man Ager"
         },
         "projectType":"type2"
      },
      "Project3id":{
         "data":{
            "customer":"Another Customer",
            "name":"Another Project"
         },
         "projectType":"type1"
      }
   }
}'

Statement with OPENJSON() and APPLY operator:

SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
   name nvarchar(max) '$.data.name',
   customer nvarchar(max) '$.data.customer',
   projectType nvarchar(max) '$.projectType',
   projectManager nvarchar(max) '$.data.projectManager'
) j2

Statement with OPENJSON() and JSON_VALUE():

SELECT 
   projectId = [key],
   name = JSON_VALUE([value], '$.data.name'),
   customer = JSON_VALUE([value], '$.data.customer'),
   projectType = JSON_VALUE([value], '$.projectType'),
   projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')

Additional edits

Source Link

edited Feb 14, 2022 at 13:00

Zhorov

29.6k
6
30
54

A possible option is usingare: 1) Using OPENJSON() twice and an additional APPLY operator or 2) Using OPENJSON() and JSON_VALUE():

JSON:

DECLARE @json nvarchar(max) = N'{
   "projects":{
      "project1id":{
         "data":{
            "customer":"Cust1",
            "name":"Project Name 1"
         },
         "projectType":"type0"
      },
      "project2id":{
         "data":{
            "customer":"Customer 2",
            "name":"Name 2",
            "projectManager":"Man Ager"
         },
         "projectType":"type2"
      },
      "Project3id":{
         "data":{
            "customer":"Another Customer",
            "name":"Another Project"
         },
         "projectType":"type1"
      }
   }
}'

Statement with OPENJSON() and APPLY operator:

SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
   name nvarchar(max) '$.data.name',
   customer nvarchar(max) '$.data.customer',
   projectType nvarchar(max) '$.projectType',
   projectManager nvarchar(max) '$.data.projectManager'
) j2

or usingStatement with OPENJSON() and JSON_VALUE():

SELECT 
   projectId = [key],
   name = JSON_VALUE([value], '$.data.name'),
   customer = JSON_VALUE([value], '$.data.customer'),
   projectType = JSON_VALUE([value], '$.projectType'),
   projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')

A possible option is using OPENJSON() twice and an additional APPLY operator:

SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
   name nvarchar(max) '$.data.name',
   customer nvarchar(max) '$.data.customer',
   projectType nvarchar(max) '$.projectType',
   projectManager nvarchar(max) '$.data.projectManager'
) j2

or using JSON_VALUE():

SELECT 
   projectId = [key],
   name = JSON_VALUE([value], '$.data.name'),
   customer = JSON_VALUE([value], '$.data.customer'),
   projectType = JSON_VALUE([value], '$.projectType'),
   projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')

A possible option are: 1) Using OPENJSON() twice and an additional APPLY operator or 2) Using OPENJSON() and JSON_VALUE():

JSON:

DECLARE @json nvarchar(max) = N'{
   "projects":{
      "project1id":{
         "data":{
            "customer":"Cust1",
            "name":"Project Name 1"
         },
         "projectType":"type0"
      },
      "project2id":{
         "data":{
            "customer":"Customer 2",
            "name":"Name 2",
            "projectManager":"Man Ager"
         },
         "projectType":"type2"
      },
      "Project3id":{
         "data":{
            "customer":"Another Customer",
            "name":"Another Project"
         },
         "projectType":"type1"
      }
   }
}'

Statement with OPENJSON() and APPLY operator:

SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
   name nvarchar(max) '$.data.name',
   customer nvarchar(max) '$.data.customer',
   projectType nvarchar(max) '$.projectType',
   projectManager nvarchar(max) '$.data.projectManager'
) j2

Statement with OPENJSON() and JSON_VALUE():

SELECT 
   projectId = [key],
   name = JSON_VALUE([value], '$.data.name'),
   customer = JSON_VALUE([value], '$.data.customer'),
   projectType = JSON_VALUE([value], '$.projectType'),
   projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')

Additional edits

Source Link

edited Feb 14, 2022 at 12:49

Zhorov

29.6k
6
30
54

Loading

Source Link

created Feb 14, 2022 at 12:43

Zhorov

29.6k
6
30
54

Loading

Collectives™ on Stack Overflow

Return to Answer