'OpenJson - nested select?
I need to import json data into an SQL table however there are some fields that I only want the numeric value from the list. Is there way to do a nested select using openjson?
I'm calling a stored procedure on a Microsoft SQL 2016 server from pyton and passing the json as a parameter.
Select * from OpenJson(@json)
WITH (id int 'strict $.id',
[Name] nvarchar(50) '$.name',
term_id nvarchar(max) as json,
phone nvarchar(20) '$.phone',
email nvarchar(50) '$.email',
street nvarchar(75) '$.street',
street2 nvarchar(75) '$.street2',
city nvarchar(50) '$.city',
zip nvarchar(20),
country_id nvarchar(max) as JSON)
The table has the following fields
ID, Name, Term_ID, Phone, Email, Street, Street2, Zip, City, Country_ID
see the json below. On the fields term_id and country_id I only want to pull the numeric value not the string.
[{
"id": 340,
"name": Joe,
"term_id": [
3,
"Net 30"
],
"phone": 123-456-7890,
"email": false,
"street": "Some Street",
"street2": false,
"zip": "01234",
"city": "SomeCity",
"country_id": [
1,
"United States"
]
}]
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
