Auflösen von JSON in SQL
select REPLACE(ShipAddr,””,‘”‘),
case when ISJSON(REPLACE(ShipAddr,””,‘”‘)) = 1 then JSON_VALUE(REPLACE(ShipAddr,””,‘”‘), ‘$.Line1’) else NULL end asLine1,
case when ISJSON(REPLACE(ShipAddr,””,‘”‘)) = 1 then JSON_VALUE(REPLACE(ShipAddr,””,‘”‘), ‘$.Line2’) else NULL end asLine2,
case when ISJSON(REPLACE(ShipAddr,””,‘”‘)) = 1 then JSON_VALUE(REPLACE(ShipAddr,””,‘”‘), ‘$.City’) else NULL end as City,
case when ISJSON(REPLACE(ShipAddr,””,‘”‘)) = 1 then JSON_VALUE(REPLACE(ShipAddr,””,‘”‘), ‘$.Country’) else NULL end asCountry,
case when ISJSON(REPLACE(ShipAddr,””,‘”‘)) = 1 then JSON_VALUE(REPLACE(ShipAddr,””,‘”‘), ‘$.CountrySubDivisionCode’)else NULL end as CountrySubDivisionCode,
case when ISJSON(REPLACE(ShipAddr,””,‘”‘)) = 1 then JSON_VALUE(REPLACE(ShipAddr,””,‘”‘), ‘$.PostalCode’) else NULL endas PostalCode
from PY_Invoice
select Invoice_Id, Line_LinkedTxn, txnline.*
from PY_Invoice_Line
outer apply OPENJSON(Line_LinkedTxn)
WITH (TxnId INT,
TxnType NVARCHAR(50),
TxnLineId INT) as txnline