Json lines (.jsonl file) & SQL Server 2016

Question

I’ve been going backwards and forwards over this but stumped. I have a file that has multiple JSON lines in it across multiple objects.

I've put two lines below.

{
    "mental_health_act_legal_status": 
    [
        {"legal_status_classification": "16", "start_time": "16:32", "expiry_date": "20171014", "expiry_time": "20:28", "start_date": "20170912"}, 
        {"legal_status_classification": "07", "start_time": "01:31", "expiry_date": "20170922", "expiry_time": "17:53", "start_date": "20170820"}, 
        {"legal_status_classification": "36", "start_time": "00:25", "expiry_date": "20170909", "expiry_time": "18:08", "start_date": "20170801"}, 
        {"legal_status_classification": "18", "start_time": "18:26", "expiry_date": "20170801", "expiry_time": "12:40", "start_date": "20170724"}, 
        {"legal_status_classification": "18", "start_time": "10:26", "expiry_date": "20170801", "expiry_time": "03:07", "start_date": "20170623"}, 
        {"legal_status_classification": "18", "start_time": "04:11", "expiry_date": "20170621", "expiry_time": "12:51", "start_date": "20170601"}, 
        {"legal_status_classification": "17", "start_time": "07:54", "expiry_date": "20170605", "expiry_time": "05:04", "start_date": "20170512"}, 
        {"legal_status_classification": "31", "start_time": "02:41", "expiry_date": "20170520", "expiry_time": "11:14", "start_date": "20170920"}, 
        {"legal_status_classification": "03", "start_time": "23:17", "expiry_date": "20170504", "expiry_time": "06:29", "start_date": "20170925"}, 
        {"legal_status_classification": "05", "start_time": "17:41", "expiry_date": "20170403", "expiry_time": "01:23", "start_date": "20170331"}, 
        {"legal_status_classification": "36", "start_time": "05:05", "expiry_date": "20170322", "expiry_time": "14:30", "start_date": "20170312"}, 
        {"legal_status_classification": "03", "start_time": "03:45", "expiry_date": "20170310", "expiry_time": "11:56", "start_date": "20170213"}, 
        {"legal_status_classification": "01", "start_time": "14:03", "expiry_date": "20170201", "expiry_time": "06:02", "start_date": "20170118"}, 
        {"legal_status_classification": "09", "start_time": "07:07", "expiry_date": "20170121", "expiry_time": "22:15", "start_date": "20170111"}
    ], 
    "system": {"record_id": "1484925971009999954", "provider_record_id": "RTD           0167983       0000046", "sus_version": "20170927072844117", "spell_id": "1484925971009999954"}
} 
{
    "mental_health_act_legal_status": 
    [
        {"legal_status_classification": "15", "start_time": "18:51", "expiry_date": "20170919", "expiry_time": "23:04", "start_date": "20170904"}, 
        {"legal_status_classification": "19", "start_time": "06:11", "expiry_date": "20170908", "expiry_time": "09:17", "start_date": "20170818"},
        {"legal_status_classification": "15", "start_time": "20:36", "expiry_date": "20170818", "expiry_time": "07:44", "start_date": "20170723"},
        {"legal_status_classification": "01", "start_time": "16:12", "expiry_date": "20170809", "expiry_time": "21:25", "start_date": "20170718"},
        {"legal_status_classification": "09", "start_time": "06:06", "expiry_date": "20170707", "expiry_time": "18:10", "start_date": "20170615"},
        {"legal_status_classification": "07", "start_time": "11:53", "expiry_date": "20170625", "expiry_time": "14:09", "start_date": "20170527"},
        {"legal_status_classification": "19", "start_time": "07:13", "expiry_date": "20170529", "expiry_time": "00:39", "start_date": "20170503"},
        {"legal_status_classification": "35", "start_time": "16:01", "expiry_date": "20170521", "expiry_time": "07:18", "start_date": "20170912"},
        {"legal_status_classification": "35", "start_time": "02:45", "expiry_date": "20170430", "expiry_time": "06:47", "start_date": "20170902"},
        {"legal_status_classification": "03", "start_time": "19:51", "expiry_date": "20170410", "expiry_time": "18:46", "start_date": "20170319"},
        {"legal_status_classification": "13", "start_time": "20:58", "expiry_date": "20170310", "expiry_time": "10:52", "start_date": "20170220"},
        {"legal_status_classification": "02", "start_time": "13:11", "expiry_date": "20170217", "expiry_time": "00:42", "start_date": "20170127"},
        {"legal_status_classification": "15", "start_time": "18:23", "expiry_date": "20170209", "expiry_time": "20:03", "start_date": "20170120"},
        {"legal_status_classification": "07", "start_time": "00:59", "expiry_date": "20170119", "expiry_time": "12:33", "start_date": "20161231"}
    ], 
    "system": {"record_id": "1484925971009999916", "provider_record_id": "RTD           0167983       0000084", "sus_version": "20170927072844117", "spell_id": "1484925971009999916"}
}

If I use the openjson syntax, I can get it to load a section I want to one of the tables but only one line in the file is loaded; but I need to go through the entire file.

I then looked at a .fmt file from a post here but from what I can tell from using this so far is that the JSON lines all have to be going to the same table. You can’t have multiple objects going to multiple tables in the line or it doesn’t know where to split them.

My code for getting this into SQL, for just two tables covering the system and mental health act legal status is:

DECLARE @JSON NVARCHAR(MAX)

SET @JSON = (SELECT J.*
             FROM OPENROWSET
             (BULK 'C:\Users\report.jsonl', SINGLE_CLOB)
             AS j)

SELECT * FROM OPENJSON (@JSON, '$.system')
    WITH ([SUS_VERSION] VARCHAR (255) '$.sus_verion',
        [SPELL_ID] VARCHAR (255) '$.spell_id',
        [RECORD_ID] VARCHAR (255)'$.record_id',
        [PROVIDER_RECORD_ID] VARCHAR (255) '$.provider_record_id'
    )

SELECT [record_id], [legal_status_classification], [start_date], [start_time], [expiry_date], [expiry_time]
FROM OPENROWSET (BULK 'C:\Users\report.jsonl', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON (BulkColumn, '$.mental_health_act_legal_status')
WITH (
    [legal_status_classification] VARCHAR (255),
    [start_time] VARCHAR (255),
    [expiry_date] VARCHAR (255),
    [expiry_time] VARCHAR (255),
    [start_date] VARCHAR (255)
    )
CROSS APPLY OPENJSON (BulkColumn, '$.system')
WITH (
    [record_id] VARCHAR (255)
    )

Is any one able to provide advice or help or what route to look at please.

Thanks

So.. are you saying the first `SELECT * FROM OPENJSON (@JSON, '$.system')` only returns the first row? First thing I'd do is check that the variable `@JSON` indeed contains multiple `"system":` rows — Nick.Mc, Oct 10 '17 at 09:33
Hi Nick. It does contain multiple "system" rows. System is the mandatory section that has to be there as it contains the record_id that is system generated. Everything is technically optional, but for the example I've added in MHA. The record_id is what identifies the MHA rows to a specific individual record rather then there being no identifier and record_id is the PK in the system block. I've copied two rows and trimmed it to just MHA and system to make it easier to see. — user1663003, Oct 10 '17 at 09:40
Basically there's nothing that indicates that `"system"` is a record in an array. My reading around `OPENJSON` indicates that you can only get multiple records out of JSON arrays. JSON arrays are in this format: `[{a thing},{anotherthing},{another one}]`. Obviously you don't have a choice to change the format of this file. I edited my answer below. That might not be the end of it but the samples appear to indicate this is the required format. — Nick.Mc, Oct 10 '17 at 10:03

Nick.Mc · Answer 1 · 2017-10-10T10:02:04.420

I don't think this is 'well formed JSON' - shouldn't there be a comma at the end if the first line? and shouldn't it be surrounded by square brackets? Did something generate this JSON?

If you take a look at the sample lifted from here: https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql you can see that multiple order records contained in curly braces are seperated by commas and surrounded by square brackets

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "Order": {  
      "Number":"SO43659",  
      "Date":"2011-05-31T00:00:00"  
    },  
    "AccountNumber":"AW29825",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":1  
    }  
  },  
  {  
    "Order": {  
      "Number":"SO43661",  
      "Date":"2011-06-01T00:00:00"  
    },  
    "AccountNumber":"AW73565",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":3  
    }  
  }
]'  

SELECT *
FROM OPENJSON ( @json )  
WITH (   
              Number   varchar(200)   '$.Order.Number',  
              Date     datetime       '$.Order.Date',  
              Customer varchar(200)   '$.AccountNumber',  
              Quantity int            '$.Item.Quantity',  
              [Order]  nvarchar(MAX)  AS JSON  
 )

You can push data off to different tables. It's not a limitation of OpenJSON, you just need to use staging tables etc to work out how to split it off. I realise this is not really an answer but I will add more detail if you're interested.

I can get multiple rows with this syntax, but only because I added square brackets for an array, and put a comma between the elements:

DECLARE @JSON VARCHAR(MAX);

SET @JSON = '
[{
    "mental_health_act_legal_status": 
    [
        {"legal_status_classification": "16", "start_time": "16:32", "expiry_date": "20171014", "expiry_time": "20:28", "start_date": "20170912"}, 
        {"legal_status_classification": "07", "start_time": "01:31", "expiry_date": "20170922", "expiry_time": "17:53", "start_date": "20170820"}, 
        {"legal_status_classification": "36", "start_time": "00:25", "expiry_date": "20170909", "expiry_time": "18:08", "start_date": "20170801"}, 
        {"legal_status_classification": "18", "start_time": "18:26", "expiry_date": "20170801", "expiry_time": "12:40", "start_date": "20170724"}, 
        {"legal_status_classification": "18", "start_time": "10:26", "expiry_date": "20170801", "expiry_time": "03:07", "start_date": "20170623"}, 
        {"legal_status_classification": "18", "start_time": "04:11", "expiry_date": "20170621", "expiry_time": "12:51", "start_date": "20170601"}, 
        {"legal_status_classification": "17", "start_time": "07:54", "expiry_date": "20170605", "expiry_time": "05:04", "start_date": "20170512"}, 
        {"legal_status_classification": "31", "start_time": "02:41", "expiry_date": "20170520", "expiry_time": "11:14", "start_date": "20170920"}, 
        {"legal_status_classification": "03", "start_time": "23:17", "expiry_date": "20170504", "expiry_time": "06:29", "start_date": "20170925"}, 
        {"legal_status_classification": "05", "start_time": "17:41", "expiry_date": "20170403", "expiry_time": "01:23", "start_date": "20170331"}, 
        {"legal_status_classification": "36", "start_time": "05:05", "expiry_date": "20170322", "expiry_time": "14:30", "start_date": "20170312"}, 
        {"legal_status_classification": "03", "start_time": "03:45", "expiry_date": "20170310", "expiry_time": "11:56", "start_date": "20170213"}, 
        {"legal_status_classification": "01", "start_time": "14:03", "expiry_date": "20170201", "expiry_time": "06:02", "start_date": "20170118"}, 
        {"legal_status_classification": "09", "start_time": "07:07", "expiry_date": "20170121", "expiry_time": "22:15", "start_date": "20170111"}
    ], 
    "system": {"record_id": "1484925971009999954", "provider_record_id": "RTD           0167983       0000046", "sus_version": "20170927072844117", "spell_id": "1484925971009999954"}
},{
    "mental_health_act_legal_status": 
    [
        {"legal_status_classification": "15", "start_time": "18:51", "expiry_date": "20170919", "expiry_time": "23:04", "start_date": "20170904"}, 
        {"legal_status_classification": "19", "start_time": "06:11", "expiry_date": "20170908", "expiry_time": "09:17", "start_date": "20170818"},
        {"legal_status_classification": "15", "start_time": "20:36", "expiry_date": "20170818", "expiry_time": "07:44", "start_date": "20170723"},
        {"legal_status_classification": "01", "start_time": "16:12", "expiry_date": "20170809", "expiry_time": "21:25", "start_date": "20170718"},
        {"legal_status_classification": "09", "start_time": "06:06", "expiry_date": "20170707", "expiry_time": "18:10", "start_date": "20170615"},
        {"legal_status_classification": "07", "start_time": "11:53", "expiry_date": "20170625", "expiry_time": "14:09", "start_date": "20170527"},
        {"legal_status_classification": "19", "start_time": "07:13", "expiry_date": "20170529", "expiry_time": "00:39", "start_date": "20170503"},
        {"legal_status_classification": "35", "start_time": "16:01", "expiry_date": "20170521", "expiry_time": "07:18", "start_date": "20170912"},
        {"legal_status_classification": "35", "start_time": "02:45", "expiry_date": "20170430", "expiry_time": "06:47", "start_date": "20170902"},
        {"legal_status_classification": "03", "start_time": "19:51", "expiry_date": "20170410", "expiry_time": "18:46", "start_date": "20170319"},
        {"legal_status_classification": "13", "start_time": "20:58", "expiry_date": "20170310", "expiry_time": "10:52", "start_date": "20170220"},
        {"legal_status_classification": "02", "start_time": "13:11", "expiry_date": "20170217", "expiry_time": "00:42", "start_date": "20170127"},
        {"legal_status_classification": "15", "start_time": "18:23", "expiry_date": "20170209", "expiry_time": "20:03", "start_date": "20170120"},
        {"legal_status_classification": "07", "start_time": "00:59", "expiry_date": "20170119", "expiry_time": "12:33", "start_date": "20161231"}
    ], 
    "system": {"record_id": "1484925971009999916", "provider_record_id": "RTD           0167983       0000084", "sus_version": "20170927072844117", "spell_id": "1484925971009999916"}
}]';


SELECT * FROM OPENJSON (@JSON)
    WITH ([SUS_VERSION] VARCHAR (255) '$.system.sus_verion',
        [SPELL_ID] VARCHAR (255) '$.system.spell_id',
        [RECORD_ID] VARCHAR (255)'$.system.record_id',
        [PROVIDER_RECORD_ID] VARCHAR (255) '$.system.provider_record_id'
    );

Hi Nick. Thanks for your comment. I have edited the post to use one of the actual lines but last night when I was posting I didn't have access as the laptop it was on had run out of power. I've also added my script for getting certain parts out, but I am writing one to do all. My issue is that when it goes to the file emergency_care.jsonl the SQL only runs on the first line it finds. I trying to make it go through every line in the file. This is all test data formed to develop the load routine. Thanks. — user1663003, Oct 10 '17 at 09:03

Json lines (.jsonl file) & SQL Server 2016

1 Answers1