0

I read the docs of handling a JSON file here. So far I am able to read the file and get a result:

QRY: SELECT *  FROM OPENROWSET (BULK 'c:\ne.db', SINGLE_CLOB) as import
Result: {"res":{"number":"123", "info":"c-PM6900"},"_id":"aHMIeu6ZwB9lIBZk"} {"res":{"number":"456", "info":"a-PMs900"},"_id":"aHaIeu6ZwB9sIBZ1"}....

if I qry this, I only get the first row with the res nested:

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\ne.db', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON)

Result

What I want to achieve, is to read every entry of the JSON file and insert "res" from the json query into a row of a table in the database containing columns "number","info","id". If anyone could help me to finish this, I would appreciate.

The JSON file contains about 400000 lines and comes from a NodeJS script which uses nedb.

Here is the example file: LINK

Improve this question
2
  • A similar Q&A. The important part is that your JSON is not valid (it contains two or more root objects), but OPENJSON() successfully parses only the first of them. You can check this with a simple SELECT ISJSON(@json) call. Commented Oct 27, 2020 at 12:00
  • I’m voting to close this question because from the comments on my answer, it appears that the code the OP has actually returns an error, not a dataset (as the image implies) and that there are other steps missing. The question suggests that the OP simply needs to consume the JSON data from OPENROWSET, however, we now know that dataset is never returned. This feels like several questions need asking, starting with the syntax error the OP is getting. Commented Oct 27, 2020 at 12:06

2 Answers 2

Reset to default
1

The JSON in the file is not a valid JSON, it contains multiple root elements or a single row for each JSON object. It's strange, but OPENJSON() reads only the first element in this JSON input without generating an error.

But you may try to transform the input JSON into a valid JSON array ({...} {...} into [{}, {...}]) and parse this JSON array with OPENJSON() and explicit schema. If the input file has a single row for each JSON object, you need to know the new line separator (it's usually CHAR(10)):

DECLARE @json nvarchar(MAX) 

-- Read the file's content
-- SELECT @json = BulkColumn
-- FROM OPENROWSET (BULK 'C:\ne.db', SINGLE_CLOB) AS [Insert]

-- Only for test
SELECT @json = 
   N'{"res":{"number":"123", "info":"c-PM6900"},"_id":"aHMIeu6ZwB9lIBZk"}' +
   CHAR(10) +
   N'{"res":{"number":"456", "info":"a-PMs900"},"_id":"aHaIeu6ZwB9sIBZ1"}'

SELECT [number], [info], [_id]
FROM OPENJSON(CONCAT('[', REPLACE(@json, CONCAT('}', CHAR(10), '{'), '},{'), ']')) WITH (
   [number] varchar(3) '$.res.number',
   [info] varchar(10) '$.res.info',
   _id varchar(50) '$._id'
)

Result:

number info     _id
123    c-PM6900 aHMIeu6ZwB9lIBZk
456    a-PMs900 aHaIeu6ZwB9sIBZ1
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you @Zhorov, thats close! The only thing what will not work if you have the data contained in a file like you commented out.
{"res":{"number":"123", "info":"c-PM6900"},"_id":"aHMIeu6ZwB9lIBZk"} {"res":{"number":"456", "info":"a-PMs900"},"_id":"aHaIeu6ZwB9sIBZ1"}
@Matthias, yes, the format of the file is important. Does it have one row for each JSON object?
@Matthias. just find the new line separator (probably CHAR(10)) and change REPLACE(@json, '} {', '},{') with REPLACE(@json, '}' + CHAR(10) + '{', '},{'). Another possible new line separator is CHAR(10) + CHAR(13).
Saved my life. 336917 rows in 6sec instead of 1,5hours with the JavaScript. Thanks so much.
|
1

You need to use a couple of calls to OPENJSON to achieve this, with a WITH:

DECLARE @JSON nvarchar(MAX) = N'{"res":{"number":"123", "info":"c-PM6900"},"_id":"aHMIeu6ZwB9lIBZk"} {"res":{"number":"456", "info":"a-PMs900"},"_id":"aHaIeu6ZwB9sIBZ1"}'

SELECT J._id,
       r.number,
       r.info
FROM OPENJSON(@JSON)
     WITH (_id varchar(30),
           res nvarchar(MAX) AS JSON) J
     CROSS APPLY OPENJSON(J.res)
                 WITH(number int,
                     info varchar(10)) r;

Because the OP appears to think I am telling them to change their DECLARE and assignment statement... to confirm how you get the value into the @JSON, from the OP's own question:

DECLARE @JSON varchar(max);
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\ne.db', SINGLE_CLOB);

Final edit, is also appears that the OP's JSON is malformed, as I would expect a comma, or something, before the second res definition. Guessing we need to split it into rows as well, which means some string splitting:

SELECT J._id,
       r.number,
       r.info
FROM STRING_SPLIT(REPLACE(@JSON,N'}} {"res"',N'}}|{"res"'),'|') SS --I assume a pipe (|`) won't appear in the data
     CROSS APPLY OPENJSON(SS.[value])
                 WITH (_id varchar(30),
                       res nvarchar(MAX) AS JSON) J
     CROSS APPLY OPENJSON(J.res)
                 WITH(number int,
                     info varchar(10)) r;

db<>fiddle

Improve this answer

23 Comments

That's your data, @Matthias . I don't have access to your instance, so I put your example JSON in a variable.
No, you already show you're getting that from the file, @Matthias ... If you read your question, you demonstrator how you set the value of @JSON. This answer doesn't change that, so i don't understand your confusion.
Use your DECLARE and SELECT exactly as you have them, @Matthias .... The solution is the OPENJSON. Me assigning a literal to the variable doesn't change anything about how you assign it.
... @Matthias check your own question, you show what to do... SELECT @JSON=BulkColumn FROM OPENROWSET (BULK 'C:\ne.db', SINGLE_CLOB) import... Again, I don't understand your confusion... I'm not telling you to change that, so why do you want to? Are you saying that wasn't working? That isn't what your question says, and if it isn't working, then you should be asking a (separate) question about that, as how to use OPENROWSET and how to consume JSON are very different questions.
That will error, @Matthias .... Why are you changing the SQL that you had that was working...? Again I never told you to change that, the solution is the OPENJSON part... If you must use DECLARE and a SELECT in the same statement, the latter must be a subquery.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.