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
select
[Transactions],
Date,
substring([Dimensionsspalten], 1,P1.Pos-1) AS BestellNr,
substring([Dimensionsspalten], P1.Pos + 1, P2.Pos – P1.Pos – 1) AS Campaign,
substring([Dimensionsspalten], P2.Pos + 1, P3.Pos – P2.Pos – 1) AS Channelgrouping,
substring([Dimensionsspalten], P3.Pos + 1, P4.Pos – P3.Pos – 1) AS Source,
substring([Dimensionsspalten], P4.Pos + 1, P5.Pos – P4.Pos – 1) AS Zeitstempel,
substring([Dimensionsspalten],P5.Pos+1,LEN([Dimensionsspalten]) ) AS Medium
from [ga_EcommerceExport]
cross apply (select (charindex(‘,’, [Dimensionsspalten]))) as P1(Pos)
cross apply (select (charindex(‘,’, [Dimensionsspalten], P1.Pos+1))) as P2(Pos)
cross apply (select (charindex(‘,’, [Dimensionsspalten], P2.Pos+1))) as P3(Pos)
cross apply (select (charindex(‘,’, [Dimensionsspalten], P3.Pos+1))) as P4(Pos)
cross apply (select (charindex(‘,’, [Dimensionsspalten], P4.Pos+1))) as P5(Pos)
cross apply (select (charindex(‘,’, [Dimensionsspalten], P5.Pos+1))) as P6(Pos)