CODEWITHSUNDEEP
sqljsonsql-server
user16669095
user16669095

Reputation: 13

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:\Users\shahk\OneDrive\Desktop\FetchAssgn\users.json', 
    FORMATFILE= 'C:\Users\shahk\OneDrive\Desktop\FetchAssgn\csv.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:

enter image description here

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

Upvotes: 0

Views: 347

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8809

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.

Upvotes: 1

Related Questions