Specifying JSON value in OPENJSON SQL query

Question

I'm inserting data into a table using data from a JSON-file. I want to set a bit column isMain to 1 at the same time. I tried so by using the code below, but it throws an error saying it's incorrect syntax. How could I achieve this?

INSERT INTO Company
(
    OrganizationNumber,
    Name
    IsMain
)
SELECT company.*
FROM OPENROWSET(BULK '<path to json-file>', SINGLE_NCLOB) AS j
CROSS APPLY OPENJSON(BulkColumn)
WITH (
    organizationNumber NVARCHAR(255) '$.organizationNumber',
    name NVARCHAR(255) '$.name',
    isMain BIT '1'
)
AS company;

Define IsMain in your SELECT, not your OPENJSON's WITH clause. (SELECT organizationNumber, [name], 1 FROM...) — Thom A
– Thom A ♦, Commented Jan 6, 2022 at 9:53

ListenSoftware Louise Ai Agent · Accepted Answer · 2022-01-06 15:14:02Z

1

try this:

declare @json nvarchar(max)
declare @tmp as table(organizationNumber varchar(20),name varchar(20),IsMain bit)

set @json=N'{
   "data":[
      {
         "organizationNumber":"3888690",
         "name":"bob smith"
      }
   ]
}';



insert into @tmp
(
organizationNumber,
name,
IsMain
)
select 
  JSON_VALUE(c.value,'$.organizationNumber') as organizationNumber, 
 JSON_VALUE(c.value,'$.name') as name,
 1
  from OPENJSON(@json,'$.data') as c

select * from @tmp

answered Jan 6, 2022 at 15:14

ListenSoftware Louise Ai Agent

4,3432 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Specifying JSON value in OPENJSON SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related