Issues parsing JSON object into sql server table

1

Hi Im having issues parsing part of a json file into sql server. I'm struggling to parse the phonenumbers object into 2 columns to identify the 2 phonenumber types: type_private and type_business . i want the dispplayed in 1 row not 2

so i want 1 row
| type_private | type_business|
|4444444444444 |8888888888888 |

not 2 rows

|type |number |
|private |4444444444444|
|business|8888888888888|

code is below but i cant get the "problem code " statements below to work. I would really appreciate any help . (Note Im new to json files and fairly new to sql server)
Thanks in advance K :slight_smile:

DECLARE @jsonInfo NVARCHAR(MAX)
SELECT @jsoninfo =
N'{
"policyholders":
{
"details": {
"contactdetails":
{
"phonenumbers": [
{
"type": "private",
"number": "4444444444444"
},
{
"type": "business",
"number": "8888888888888"
}
]
}
}
}
}'

SELECT *
FROM OPENJSON (@jsoninfo, '$.policyholders' )

WITH ( details nvarchar(max) as json ) as policyholders_1
CROSS APPLY OPENJSON (policyholders_1.details)
WITH ( contactdetails nvarchar(max) as json ) as details_2
CROSS APPLY OPENJSON(details_2.contactdetails)
WITH (phonenumbers nvarchar(max) as json ) as contactdetails_3
--problem code start
cross apply ( select ( JSON_QUERY(@jsoninfo,'$.phonenumbers[0]') ) )
as phonenumbers_private_4 ([phonenumber_private_json])
cross apply ( select ( JSON_QUERY(@jsoninfo,'$.phonenumbers[1]') ) )
as phonenumbers_business_4 ([phonenumber_business_json])
--problem code end

2

Just use variations on:

SELECT type_private, type_business
FROM OPENJSON(@jsoninfo)
WITH
(
	type_private varchar(20) '$.policyholders.details.contactdetails.phonenumbers[0].number'
	,type_business varchar(20) '$.policyholders.details.contactdetails.phonenumbers[1].number'
);
3

or, if the index will not always be the same, CROSS APPLY to the following pivot:

SELECT MAX(CASE WHEN P.[type] = 'private' THEN P.number END) AS type_private
	,MAX(CASE WHEN P.[type] = 'business' THEN P.number END) AS type_business
FROM OPENJSON(@jsoninfo, '$.policyholders.details.contactdetails.phonenumbers')
WITH
(
	[type] varchar(20) '$.type'
	,number varchar(20) '$.number'
) P
4

Hi there. Thanks for your suggestions. These worked for the test code I used when only parsing the phonenumbers in isolation , however both solutions didnt work on my real data when selecting more columns and using more openjson clauses . Not quite sure why though. All the levels of arays have been included , the only thing that is missing is other data within the arrays .

Please see my updated code below which is more like my real data and demonstrates that the issues still exists) NB apologies that my code doenst like nicely formatted and coloured like yours. i'm new to this forum so not sure how to format things properly yet)

In summary:

  • solution 1 - gave null values for the type_private and type_buisness columns
  • solution 2 - gave the error "'Column 'policyholders_1.identifier' is invalid in the select
    list because it is not contained in either an aggregate function or the GROUP BY clause.( im guessing this is because im using an aggregate function?? if so how would i fix this? )

I would really appreicate if you have any other suggestions based on this update?

Kind Regards. K :slight_smile:

-------------------------updated code below ---------
DECLARE @jsonInfo NVARCHAR(MAX)
SELECT @jsoninfo =
N'{
"policyholders":
[
{
"details":
{
"surname": "xxxxx",
"forenames": [
"yyyyy"
],
"title": {
"list": "53",
"value": "002",
"description": "MISS"
},
"gender": {
"list": "5",
"value": "F",
"description": "Female"
},
"dateofbirth": "1111-11-11",
"contactdetails":
{
"phonenumbers": [
{
"type": "private",
"number": "444444444444"
},
{
"type": "business",
"number": "888888888888"
}
],
"physicaladdress": {
"postcode": "XXX XXX",
"addresslines": [
"XXXXXXX",
"YYYYYYY",
"ZZZZZZZ"
],
"isforeignaddress": false
}
}
}
}
]
}'

SELECT
policyholders_1.identifier,
p1.type_private ,
p1.type_business,

MAX(CASE WHEN p2.[type] = 'private' THEN p2.number END) AS type_private,
MAX(CASE WHEN p2.[type] = 'business' THEN p2.number END) AS type_business

FROM OPENJSON (@JSONINFO, '$.policyholders' )

WITH ( details nvarchar(max) as json , identifier varchar(10) ) as policyholders_1
CROSS APPLY OPENJSON (policyholders_1.details)
WITH (contactdetails nvarchar(max) as json ) as details_2
CROSS APPLY OPENJSON(details_2.contactdetails)
WITH (phonenumbers nvarchar(max) as json ) as contactdetails_3

--solution1
CROSS APPLY OPENJSON(@jsoninfo)
WITH
( type_private varchar(20) '$.policyholders.details.contactdetails.phonenumbers[0].number',
type_business varchar(20) '$.policyholders.details.contactdetails.phonenumbers[1].number'
) as p1

--solution 2
cross apply OPENJSON(@jsoninfo, '$.policyholders.details.contactdetails.phonenumbers')
WITH([type] varchar(20) '$.type',number varchar(20) '$.number') p2

5 
SELECT D.surname, D.forename1, D.forename2, D.forename3, D.title, D.sex, D.dateofbirth
	,X.private_number, X.business_number
	,D.addressline1, D.addressline2, D.addressline3, D.addressline4
	,D.postcode, D.isforeignaddress
FROM OPENJSON(@jsoninfo, '$.policyholders')
WITH
(
	details nvarchar(MAX) '$.details' AS JSON
) P
	CROSS APPLY OPENJSON(P.details)
	WITH
	(
		surname nvarchar(50)
		,forename1 nvarchar(20) '$.forenames[0]'
		,forename2 nvarchar(20) '$.forenames[1]'
		,forename3 nvarchar(20) '$.forenames[2]'
		,title nvarchar(20) '$.title.description'
		,sex varchar(20) '$.gender.description'
		,dateofbirth date
		,phonenumbers nvarchar(MAX) '$.contactdetails.phonenumbers' AS JSON
		,addressline1 nvarchar(30) '$.contactdetails.physicaladdress.addresslines[0]'
		,addressline2 nvarchar(30) '$.contactdetails.physicaladdress.addresslines[1]'
		,addressline3 nvarchar(30) '$.contactdetails.physicaladdress.addresslines[2]'
		,addressline4 nvarchar(30) '$.contactdetails.physicaladdress.addresslines[3]'
		,postcode nvarchar(30) '$.contactdetails.physicaladdress.postcode'
		,isforeignaddress bit '$.contactdetails.physicaladdress.isforeignaddress'
	) D
	CROSS APPLY
	(
		SELECT MAX(CASE WHEN N.[type] = 'private' THEN N.number ELSE '' END) AS private_number
			,MAX(CASE WHEN N.[type] = 'business' THEN N.number ELSE '' END) AS business_number
		FROM OPENJSON(D.phonenumbers)
		WITH
		(
			[type] varchar(20) '$.type'
			,number varchar(20) '$.number'
		) N
	) X

To get code blocks just use code, surrounded by square brackets, to start block and /code, surrounded by square brackets, to end block

1 Like
6

Thank you so much. that has solved my problem and i will use this technique going forward. :slight_smile: