openjson : SELECT star

Question

I have a table in a json file (C:\db.json):

[{"_id":"81743561","_record_number":"1","_form_id":"618213","_user_id":"218754",...},
{"_id":"81782299","_record_number":"2","_form_id":"618213","_user_id":"218754",...},
{"_id":"81784481","_record_number":"3","_form_id":"618213","_user_id":"218754",...}]

It has about 60 "columns". I have a 100 "tables" like this to import into SQL Server, each with its own schema. I found it could be done this way:

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\db.json', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON)
WITH 
(
    _id integer,
    _record_number integer,
    _form_id integer,
    _user_id integer,
    ...
)

I really need to avoid manually writing the list of columns. One option is to parse the json and generate the column list. Before I start going down that road, I was wondering if there is a way to avoid listing the columns, something equivalent to a SELECT * ?

Ethelyn

Probably a dynamic statement is an option. OPENJSON with default schema (without the WITH clause) returns one row for each key: value pair for each object in the JSON array. And the return values are varchar\nvarchar. What are the expected data type for the results from OPENJSON call - always integer? And what is your SQL Server version? – Phenica

if column data type is varchar(max) by default, it would be ok. I tried without the WITH clause, it only produced 3 columns (key, value, type). – Ethelyn

Answer 1

One possible approach is to build and execute a dynamic statement, because OPENJSON() with default schema (without the WITH clause) returns a table with columns key, value and type and one row for each "key": "value" pair for each object in the input JSON array. So, you need an explicit schema. To build that schema dynamically, you need to parse the first item from the whole JSON array using OPENJSON() with a default schema.

Statement:

DECLARE @json varchar(max)

SET @json = N'[
   {"_id":"81743561","_record_number":"1","_form_id":"618213","_user_id":"218754"},
   {"_id":"81782299","_record_number":"2","_form_id":"618213","_user_id":"218754"},
   {"_id":"81784481","_record_number":"3","_form_id":"618213","_user_id":"218754"}
]'
-- Or read JSON from file
-- SELECT @json = BulkColumn
-- FROM OPENROWSET (BULK 'C:\db.json', SINGLE_CLOB)

DECLARE @stm nvarchar(max) 

SELECT @stm = STRING_AGG(CAST([key] + ' varchar(max) ' AS varchar(max)), ', ')
FROM OPENJSON(@JSON, '$[0]')

SET @stm = N'SELECT * FROM OPENJSON (@json) WITH (' + @stm + N')'

PRINT @stm
EXEC sp_executesql @stm, N'@json varchar(max)', @json

Result:

_id      _record_number _form_id    _user_id
81743561 1              618213      218754
81782299 2              618213      218754
81784481 3              618213      218754

Phenica