Create JSON file and import as a table

Question

Hi,

This should be simple, but I tried various options and can not get it to work. I'm trying the following:

SELECT TOP (100) ADMIN_AUDIT.admin_audit_id AS [id]
			   , ADMIN_AUDIT.admin_audit_dttm AS [time]
			   , ADMIN_AUDIT.process_txt AS [process]
			   , ADMIN_AUDIT.sub_process_txt AS [subprocess]
			   
		 FROM dbo.ADMIN_AUDIT FOR JSON AUTO, INCLUDE_NULL_VALUES

Then I right click on the result, save as, save as file.json. Then I edit the file in Notepad++ and remove the identifier at the top.

After that I'm trying

SELECT ADMIN_AUDIT.*
FROM OPENROWSET(BULK 'E:\SSIS_ROOT\VA\test\admin_audit.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id bigint,
    time DATETIME2(7),
    process VARCHAR(50),
    subprocess VARCHAR(50)
) AS ADMIN_AUDIT;

And I'm getting a single row (top row) as a result. What should I change in my process to read all the rows?

Thanks in advance.

Answer 1

In SSMS, you need to beware that if you save to file, there is a max number of character per column defined in Results to Text:

I found that when I saved to file, there was a line break inserted after each 256 characters, and it went downhill from there.

Instead the correct action is to copy and paste to your text file.

In Azure Data Studio, Save as JSON looks like a good idea, but that is a general feature to save any result set as JSON. But click on the JSON document for it to open in a new window. Here you can save to a file. Or copy-paste into a window.

Neither of the methods are particularly good for automation, but if you only do it only occasionally, it may be good enough.

Answer 2

did you remove the []'s

the json (formatted) should look like:

[
    {
        "id": 1,
        "time": "2024-11-27T14:39:43.917",
        "process": "process1",
        "subprocess": "subprocess1"
    },
    {
        "id": 2,
        "time": "2024-11-27T14:39:43.917",
        "process": "process2",
        "subprocess": "subprocess2"
    }
]