Importing JSON data to SQL Server

Sample data looks like this:

{
  "_id": {
    "$oid": "5ff1e194b6a9d73a3a9f1052"
  },
  "active": true,
  "createdDate": {
    "$date": 1609687444800
  },
  "lastLogin": {
    "$date": 1609687537858
  },
  "role": "consumer",
  "signUpSource": "Email",
  "state": "WI"
}

Here’s the extended sample

{"_id":{"$oid":"5ff1e194b6a9d73a3a9f1052"},"active":true,"createdDate":{"$date":1609687444800},"lastLogin":{"$date":1609687537858},"role":"consumer","signUpSource":"Email","state":"WI"}

{"_id":{"$oid":"5ff1e194b6a9d73a3a9f1052"},"active":true,"createdDate":{"$date":1609687444800},"lastLogin":{"$date":1609687537858},"role":"consumer","signUpSource":"Email","state":"WI"}

{"_id":{"$oid":"5ff1e194b6a9d73a3a9f1052"},"active":true,"createdDate":{"$date":1609687444800},"lastLogin":{"$date":1609687537858},"role":"consumer","signUpSource":"Email","state":"WI"}

{"_id":{"$oid":"5ff1e1eacfcf6c399c274ae6"},"active":true,"createdDate":{"$date":1609687530554},"lastLogin":{"$date":1609687530597},"role":"consumer","signUpSource":"Email","state":"WI"}

What I have tried –

SELECT * FROM OPENROWSET (
    BULK 'C:UsersshahkOneDriveDesktopFetchAssgnusers.json', 
    FORMATFILE= 'C:UsersshahkOneDriveDesktopFetchAssgncsv.fmt') AS [Json]
    CROSS APPLY OPENJSON (json)
    WITH  (
oid varchar(50)  ,
state varchar(5)     ,
Date datetime   ,
date datetime   ,
role varchar(10)    ,
signUpSource varchar(10),
active varchar(10)   
              
        ) AS users

Output I get:

What I require is the output without Null in id, createdDate and lastLogin columns.

Answer

You’re probably looking for something like this…

declare @json nvarchar(max) = N'
{
  "_id": {
    "$oid": "5ff1e194b6a9d73a3a9f1052"
  },
  "active": true,
  "createdDate": {
    "$date": 1609687444800
  },
  "lastLogin": {
    "$date": 1609687537858
  },
  "role": "consumer",
  "signUpSource": "Email",
  "state": "WI"
}';

select oid, state, createdDate, lastLogin, role, signUpSource, active
from openjson(@json) WITH  (
  oid varchar(50) '$._id."$oid"',
  state varchar(5),
  cd bigint '$.createdDate."$date"',
  ll bigint '$.lastLogin."$date"',
  role varchar(10)    ,
  signUpSource varchar(10),
  active varchar(10)   
) AS users
cross apply (
  select
    createdDate = dateadd(ss, cd/1000, '1970-01-01'),
    lastLogin = dateadd(ss, ll/1000, '1970-01-01')
) calc;

Which yields…

oid	state	createdDate	lastLogin	role	signUpSource	active
5ff1e194b6a9d73a3a9f1052	WI	2021-01-03 15:24:04.000	2021-01-03 15:25:37.000	consumer	Email	true

There are a couple of tricks to be aware of here:

1. In SQL Server JSON path expressions don’t support bare $ characters in property names. Properties that include $ characters need to be quoted, so $oid needs to be "$oid", $date needs to be "$date", etc..

2. On SQL Server the Unix epoch $date values will overflow int storage so need to be deserialized as bigint types.

3. The dateadd function’s numeric parameter is of type int, so to avoid Arithmetic overflow error converting expression to data type int errors you’ll need to divide the millisecond values by 1,000 and use them as seconds instead.