python (65.1k questions)

javascript (44.2k questions)

reactjs (22.7k questions)

java (20.8k questions)

c# (17.4k questions)

html (16.3k questions)

r (13.7k questions)

android (12.9k questions)

Questions - open-json

Using OPENJSON in SQL Server Query

I'm trying to pull some information from a JSON array stored in an SQL Server database. I can see I need to use OPENJSON, and I've found numerous examples of how to use it, but the rub is all of the e...
test-img

Steve

sql

json

sql-server

open-json

Votes: 0

Answers: 1

Latest Answer

You need an OPENJSON() call with the appropriate path to access the nested $.source_info.help_info.details JSON array and STRING_AGG() to aggregate the names: Sample data: SELECT * INTO Data FROM (VAL...
test-img

Zhorov

SQL on US Census - Null values return

I am trying to get data from the US Census on SQL Server Studio for analysis (trade data at port level). I have downloaded a JSON file for now from their API (ideally, I will do a call from SQL studio...
test-img

Alex

json

sql-server

census

open-json

Votes: 0

Answers: 1

Latest Answer

Please try the following solution. Your JSON is a JSON array, so it needs a slightly different syntax. SQL DECLARE @JSON VARCHAR(MAX); SELECT @JSON=BulkColumn FROM OPENROWSET (BULK 'C:\Users\amartine...
test-img

Yitzhak Khabinsky

How Can I use OpenJson to access double nested arrays IN T-SQL?

I have a JSON file listed below and I am having issues using OPENJSON to get access to the Misc field when the CheckboxValue is true. So the example below would return a table: Company ReferenceDuplic...
test-img

apolloSN

json

sql-server

tsql

open-json

Votes: 0

Answers: 1

Latest Answer

Use an open_json to get a table of arrays and then cross apply against those values. declare @S nvarchar(max) = 'JSON GOES HERE ...'; select json_value(A2.value, '$.Misc.Company') as Company, ...
test-img

Mikael Eriksson

How to use OPENJSON if column is same in SQL?

My database table is created as : declare @library table (dependencies varchar(max)) insert into @library values('{"jar":"azure.com","maven":"azure.com","...
test-img

blac040

sql

json

sql-server

open-json

Votes: 0

Answers: 1

Latest Answer

You can't do this using OPENJSON with a schema, because JSON normally expects only unique property names. You can use OPENJSON without a schema, but you also cannot use FOR JSON to create such a JSON ...
test-img

Charlieface

Posts

Questions

Blogs

Jobs