is there a good way to import .json file content using .sql script file?

Question

we have SQL project in Visual Studio with several .sql script files in it to populate data to Microsoft SQL Server. some .sql scripts import JSON data, which now is defined as a raw string inside sql files. I was thinking on extracting JSONs to separate files. What I found is examples like this

SELECT * FROM OPENROWSET (BULK '**C:\**sampledata.txt', SINGLE_CLOB) as importData

but they all have full path to source file, which is ridiculous and probably fail if run on another machine with different folder structure. Is there a way to embed .json files into SQL project and reference them from .sql file in more flexible way, to guarantee that compiled script will work?

Trevor Burn · Accepted Answer · 2021-04-07 22:04:29Z

1

I've looked into this myself, and I don't think it'll work.

The OPENROWSET BULK method of reading data from a file only accepts a full path.
- You could try and get around this with a pre/post-deployment script and some SQLCMD variables, but then you run into the next issue.
OPENROWSET will only work if the file can be accessed from the machine running the query. If you run this against a remote server, you'll have to jump through some hoops to give it access to your data file.
Also, if you want to do this from an Azure SQL database, you'll need to store the data in Azure Blob Storage.

OPENROWSET documentation

Here's the best recommendation I can provide. Assuming that your primary goals are to a) ensure legibility in the code and b) allow for a more clear diff when updating the JSON data, create separate scalar-valued functions for each of your JSON strings. Each function will simply return your raw JSON string. Not exactly creative, but it will allow you to format your JSON for maximum legibility while allowing you to clean up the scripts that actually use this data.

e.g. Instead of having one file that looks like this:

DECLARE @json NVARCHAR(MAX) = N'[{"attr":"value 1"},{"attr":"value 2"},{"attr":"value 3"},{"attr":"value 4"},{"attr":"value 5"},{"attr":"value 6"},{"attr":"value 7"},{"attr":"value 8"},{"attr":"value 9"},{"attr":"value 10"}]'
--do stuff with the @json

Have two files, one:

CREATE FUNCTION GetJson()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN N'
[
  {
    "attr": "value 1"
  },
  {
    "attr": "value 2"
  },
  {
    "attr": "value 3"
  },
  {
    "attr": "value 4"
  },
  {
    "attr": "value 5"
  },
  {
    "attr": "value 6"
  },
  {
    "attr": "value 7"
  },
  {
    "attr": "value 8"
  },
  {
    "attr": "value 9"
  },
  {
    "attr": "value 10"
  }
]
'
END

and two:

DECLARE @json NVARCHAR(MAX) = dbo.GetJson()
--do stuff with @json

answered Apr 7, 2021 at 22:04

Trevor Burn

112 bronze badges

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

2 Comments

Dmytro Kosh Over a year ago

oof, not exactly what I want, but thanks for suggestion anyways. The main issue is still JSON not being in .json file, so when you edit the thing, you edit a string in .sql file, not .json file...

Trevor Burn Over a year ago

I suppose the other way would be to set up some sort of pre-build script to properly handle single quotes in your .json file and insert the result into your .sql file before the .dacpac is generated.

Collectives™ on Stack Overflow

is there a good way to import .json file content using .sql script file?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related