I am trying to compare the set of two JSON values for every transaction and extract specific values from the below.. I want to extract the following values cCode,dCode,hcps and mod..Can you please guide me on the snowflake SQL syntax for the same.. The first JSON is done by coder and the second json by auditor
1st json
[
{
"cCode": "7832",
"Date": "08/26/2020",
"ID": "511",
"description": "holos",
"dgoses": [
{
"description": "disease",
"dCode": "Y564",
"CodeAllId": "8921",
"messages": [
""
],
"sequenceNumber": 1
},
{
"description": "acute pain",
"dCode": "U3321",
"CodeAllId": "33213",
"messages": [
""
],
"sequenceNumber": 2
},
{
"description": "height",
"dCode": "U1111",
"CodeAllId": "33278",
"messages": [
""
],
"sequenceNumber": 3
},
{
"description": "PIDEMIA ",
"dCode": "H8811",
"CodeAllId": "90000",
"messages": [
""
],
"sequenceNumber": 4
}
],
"familyPlan": "",
"hcpc": 5,
"id": "",
"isEPS": false,
"mod": "67",
"originalUnitAmount": "8888",
"type": "CHARGE",
"unitAmount": "9000",
"vId": "90001"
},
{
"cCode": "900114",
"Date": "08/26/2020",
"ID": "523",
"description": "heart valve",
"dgoses": [
{
"description": "Fever",
"dCode": "J8923",
"CodeAllId": "892138",
"messages": [
""
],
"sequenceNumber": 1
}
],
"familyPlan": "",
"hcpc": 1,
"id": "",
"mod": "26",
"originalUnitAmount": "19039",
"type": "CHARGE",
"unitAmount": "1039",
"vId": "5113"
}
]
2nd JSON
[
{
""cCode"": ""78832"",
""Date"": ""08/26/2020"",
""ID"": ""511"",
""description"": ""holos"",
""dgoses"": [
{
""description"": ""disease"",
""dCode"": ""Y564"",
""CodeAllId"": ""8921"",
""messages"": [
""""
],
""sequenceNumber"": 1
},
{
""description"": ""acute pain"",
""dCode"": ""U3321"",
""CodeAllId"": ""33213"",
""messages"": [
""""
],
""sequenceNumber"": 2
},
{
""description"": ""height"",
""dCode"": ""U41111"",
""CodeAllId"": ""33278"",
""messages"": [
""""
],
""sequenceNumber"": 3
},
{
""description"": ""PIDEMIA "",
""dCode"": ""H8811"",
""CodeAllId"": ""90000"",
""messages"": [
""""
],
""sequenceNumber"": 4
}
],
""familyPlan"": """",
""hcpc"": 8,
""id"": """",
""isEPS"": false,
""mod"": ""67"",
""originalUnitAmount"": ""8888"",
""type"": ""CHARGE"",
""unitAmount"": ""9000"",
""vId"": ""90001""
},
{
""cCode"": ""900114"",
""Date"": ""08/26/2020"",
""ID"": ""523"",
""description"": ""heart valve"",
""dgoses"": [
{
""description"": ""Fever"",
""dCode"": ""J8923"",
""CodeAllId"": ""892138"",
""messages"": [
""""
],
""sequenceNumber"": 1
}
],
""familyPlan"": """",
""hcpc"": 1,
""id"": """",
""mod"": ""126"",
""originalUnitAmount"": ""19039"",
""type"": ""CHARGE"",
""unitAmount"": ""1039"",
""vId"": ""5113""
}
]
And I am looking for a result as the below:
Billid ctextid cCode-Coder cCode-Auditor deletedccode added-ccode dCode-Coder dCode-Auditor deleted-dcode added-dcode hcpc-coder hcpc-auditor deletedhcpc addedhcpc mod-coder mod-auditor deletedmod addedmod 7111 89321 7832,900114 78832,900114 7832 78832 Y564,U3321,U1111,H8811,J8923 Y564,U3321,U41111,H8811,J8923 U1111 U41111 5,1 8,1 5 8 67,26 67,126 26 126
Can anyone please help me here
sql Tried
with cte4 as ( select info:dCode as dtcode from cte3, lateral flatten( input => saveinfo:dgoses ) ) select dCode from cte4, lateral flatten( input => dtcode )
This gives an error straightaway for using :
I have tried the code with the SQL server version but I need to know how to map the JSON functions to the Snowflake SQL version..Can you please help here..
with I as
( select , dense_rank() over (order by Billid, Ctextid) as tid, dense_rank() over (partition by Billid, Ctextid order by Created) as n from ##input1 ), D as ( select I., mk.[key] as mk, m., dk.[key] as dk, d. from I cross apply openjson(info) mk cross apply openjson(mk.value) with ( cCode nvarchar(max) ‘$.cCode’, dgoses nvarchar(max) ‘$.dgoses’ as json ) m cross apply openjson(dgoses) dk cross apply openjson(dk.value) with ( dCode nvarchar(max) ‘$.dCode’ ) d ), C as ( select * from D where n = 1 ), A as ( select * from D where n = 2 ) select Billid, codedby, Ctextid,
(
select string_agg(cCode, ',') within group (order by mk)
from
(
select distinct cCode, mk
from C
where tid = t.tid
) d
) as cCodeCoder,
(
select string_agg(cCode, ',') within group (order by mk)
from
(
select distinct cCode, mk
from A
where tid = t.tid
) d
) as cCodeAuditor,
(
select string_agg(cCode, ',')
from
(
select cCode
from C
where tid = t.tid
except
select cCode
from A
where tid = t.tid
) d
) as deletedcCode,
(
select string_agg(cCode, ',')
from
(
select cCode
from A
where tid = t.tid
except
select cCode
from C
where tid = t.tid
) d
) as addedcCode,
(
select string_agg(dCode, ',') within group (order by mk, dk)
from
(
select distinct dCode, mk, dk
from C
where tid = t.tid
) d
) as dCodeCoder,
(
select string_agg(dCode, ',') within group (order by mk, dk)
from
(
select distinct dCode, mk, dk
from A
where tid = t.tid
) d
) as dCodeAuditor,
(
select string_agg(dCode, ',')
from
(
select dCode
from C
where tid = t.tid
except
select dCode
from A
where tid = t.tid
) d
) as deleteddCode,
(
select string_agg(dCode, ',')
from
(
select dCode
from A
where tid = t.tid
except
select dCode
from C
where tid = t.tid
) d
) as addeddCode
from I as t where n = 1
Thanks, Arun
Advertisement
Answer
I’m not entirely sure how you need the data, but you’re trying to get “cCode, dCode, hcps and mod” (assuming hcps is actually hcpc). The problem is cCode, hcpc, and mod are all on the same level of the JSON. dCode is not. It’s nested one layer down from the other properties and is a one to many relationship. This could be flattened out to two tables with a 1:MANY relationship, or it could be flattened out in a single table repeating the cCode, hcpc, and mod values. This example shows the second option:
-- I created a table named FOO and added your JSON as a variant
create temp table foo(v variant);
with
JSON(C_CODE, DGOSES, HCPC, "MOD") as
(
select "VALUE":cCode::int as C_CODE
,"VALUE":dgoses as DGOSES
,"VALUE":hcpc::int as HCPS
,"VALUE":mod::int as "MOD"
from foo, lateral flatten(v)
)
select C_CODE, HCPC, "MOD", "VALUE":dCode::string as D_CODE
from JSON, lateral flatten(DGOSES);
This creates a table like this:
C_CODE HCPC MOD D_CODE 7832 5 67 Y564 7832 5 67 U3321 7832 5 67 U1111 7832 5 67 HBB11 900114 1 26 J8923