Stack Overflow
Stack Overflow

Questions tagged [open-json]

OpenJson is a T-SQL (SQL Server) built in table valued function, introduced in 2016 version. Use this tag with questions related to this command.

OpenJson was introduced in SQL Server 2016.
It takes in a nvarchar containing JSON data, with an optional nvarchar string containing a JSON Path Expression, and an optional With clause.

When the With clause is not supplied, OpenJson will return a table containing three columns:
Key (nvarchar(4000)), Value(nvarchar(max)) and Type (int).
values of the Type columns indicates the following:

Type    JSON data type
0       null
1       string
2       int
3       true/false
4       array
5       object

The With clause allows the user to explicitly define the columns of the table returned.
The With clause contains three parameters for each value to get from the JSON:
Column name, data type, and an optional JSON Path expression (if not supplied, OPENJSON will try to get a property with the same name as the target column.

If the json property extracted contains an inner json object, the AS JSON option can be specified with this column, to enable further JSON processing.

Articles

197 questions
0
votes
1 answer

How to escape characters in JSON response for SQL INSERT FROM OPENJSON via PYTHON

Getting error when sending API JSON response to SQL Server db due to apostrophe in the values ('). See second JSON response for operator_country_lar Here is a sample API response: json_response= '{ "num_results": 455161, "results": [ { …
Alen Giliana
  • 2,144
  • 3
  • 17
  • 30
0
votes
3 answers

OPENJSON does not select all documents into the SQL table

I have been trying to export the contents of a JSON file to an SQL Server table. However, despite the presence of multiple rows in the JSON, the output SQL table consists of only the first row from the JSON. The code I am using is as follows: DROP…
Darsh Khetan
  • 43
  • 5
0
votes
1 answer

Importing nested name-value pairs from json into SQL Server

I am importing a json file into SQL 2016 which has some nested name-value pairs in a nested values structure. It's the values from these pairs I am having problems with, such as . { "name": "Colour", "value": "Orange" }, { "name": "Calories",…
Fetchez la vache
  • 4,940
  • 4
  • 36
  • 55
0
votes
1 answer

SSMS: OPENJSON works but not with direct REST call

Following this article I'm trying to implement a query that could retrieve data from a REST call and parse the JSON with OPENJSON. I call this URL with Postman to check if the data are alright: The data are there. I copy paste the JSON and I test…
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113
0
votes
1 answer

How to query a value from json array type column from a table in sql server?

I have a table with a single row of data which has a nvarchar(MAX) type column, lets name the column as json_column and the values of the column are like below: { "data": [ { "name": "john", "num": "123" }, { …
Karthik
  • 629
  • 2
  • 8
  • 12
0
votes
1 answer

Create table from openjson(@json) results Azure SQL

I am trying to make a table out of the [key] row values from a select * from openjson(@json) statement. The openjson(@json) statement gives me results that contains 53 [key] row values, here's a small snippet: [key] [value] …
Katt
  • 21
  • 1
  • 4
-1
votes
1 answer

Parse JSON from tsql

I need to parse JSON using TSQL. I find a function to create a temporary table contains all values of the json and the mode to find every elements of it. Not there are an issue. I have a JSON with an array of int value, negative and positive value.…
bircastri
  • 2,169
  • 13
  • 50
  • 119
-1
votes
1 answer

How can I modify JSON object from a column while retrieving the data in SQL Server

I have a JSON array object like below in a SQL Server table column: [ { "Name": "test1", "Favouties": { "LikedColor": "Red", "ReasonsToLike": "None", "HatedColor": "White", "DatesToRemember": "2022-03-03T00:00:00" …
saikrishna
  • 11
  • 2
-1
votes
1 answer

OPENJSON() returns 0 rows from SQLCMD, but returns many rows in SSMS

As the title says, I have a set of SQL queries using OPENJSON() that work beautifully in SQL Server Management Studio, but when ran from the Command Prompt using SQLCMD, the queries return 0 rows. Example: INSERT INTO [common].[dbo].[distrcd]…
mortodestructo
  • 1
  • 1
-1
votes
1 answer

SQL Server Parse JSON Object to Rows

Thanks in advance for any help. This is killing me! :-) I have a JSON string that has an object collection nested within it that I need to get a standard SQL result set from. Here is a sample string of the JSON I am working with: { "ST":…
DanielG
  • 1,669
  • 1
  • 12
  • 26
-1
votes
2 answers

Parsing JSON from SQL Server

I have a table with the following columns, one column being a JSON blob. I'm unclear how to parse the JSON blob as a series of columns alongside the other columns. I know there's something called OPENJSON, but not sure how to apply it to this…
RalphBiggerton
  • 179
  • 5
  • 19
-1
votes
1 answer

SQL JSON_VALUE query but without path names?

I have json data that I am trying to import in to a SQL Server database but it doesnt have path names to reference - how do I go by indentifying the different elements here: Sample json:…
optionone
  • 3
  • 4
-1
votes
2 answers

JSON SQL Server 2016 parse

I have a nested JSON as output form a form and I need to parse it in order to send it in joined tables. I cannot parse some of the data I have in the JSON: labels, rangesValues or body I get NULL anyone encountered this type? DECLARE @json…
drcz
  • 23
  • 4
-1
votes
1 answer

How to load OPENJSON from Dynamic Nodes in SQL Server

The JSON format I'm receiving via REST API has dynamic nodes which I am unable to load into OPENJSON. The JSON response is from a third party and I am unable to alter it. See example below and notice the dates are being used as nodes: { "Meta…
JDG
  • 89
  • 1
  • 9
-2
votes
1 answer

Python: difficulty in parsing file as JSON

I have uploaded a file here that I am trying to parse as JSON, but my code below is not working: with open('all_links_dict.json') as fd: json_data = json.load(fd) print(json_data)
Alberi
  • 13
  • 4
1 2 3
…
13
14