I have a SQL-server, in one table some data are stored as a JSON object.

How do "split" this info into separate columns like this: Result

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;

db<>fiddle

As mentioned, single-quotes are not valid as JSON delimiters, so you need to fix that up.