I have a SQL-server, in one table some data are stored as a JSON object.
ID | Date | Customer details |
---|---|---|
1 | 2022-01-01 | {'Country':167,'AccountNumber':'123456','SwiftBic':'ABC123'} |
2 | 2022-01-01 | {'Country':666,'AccountNumber':'765432','SwiftBic':'XYZ123'} |
How do "split" this info into separate columns like this: Result
ID | Date | Country | AccountNumber | SwiftBic |
---|---|---|---|---|
1 | 2022-01-01 | 167 | 123456 | ABC123 |
2 | 2022-01-01 | 666 | 765432 | XYZ123 |
CodePudding user response:
You can use JSON_VALUE for this :
SELECT
ID,
Details,
JSON_VALUE(details,'$.Country') AS Country,
JSON_VALUE(details,'$.AccountNumber') AS AccountNumber,
JSON_VALUE(details,'$.SwiftBic') AS SwiftBic
FROM ThatTable
If you need to use these columns frequently, especially if you need to use them in WHERE
clauses, you can create computed columns using JSON_VALUE
and index them:
CREATE TABLE ThatTable (
...
Country AS JSON_VALUE(CustomerDetails, '$.Country') PERSISTED,
...
)
CREATE INDEX IX_ThatTable_Country ON dbo.ThatTable(Country);
It's not possible to expand arbitrary properties to columns. The columns in a query are similar to types in a strongly typed language and have to be known in advance.
Single or Double quotes?
The JSON Standard specifies that only double-quotes can be used as quotation marks in JSON.
quotation-mark = %x22 ; "
Parsers and serializers must use and understand "
as a quotation mark but are free to reject '
. Using '
can lead to problems.
T-SQL is strict about this, so the following is invalid :
{'Country':167,'AccountNumber':'123456','SwiftBic':'ABC123'}
While this is valid:
{"Country":167,"AccountNumber":"123456","SwiftBic":"ABC123"}
CodePudding user response:
Another option, which is easier if you have many properties to pull out, is to use OPENJSON
SELECT
t.ID,
t.Date,
j.*
FROM YourTable t
OUTER APPLY OPENJSON(t.details)
WITH (
Country varchar(30),
AccountNumber varchar(10),
SwiftBic varchar(30)
) j;
As mentioned, single-quotes are not valid as JSON delimiters, so you need to fix that up.