Access and save fields from a JSON in a SQL Server table

user_8962304 0
Viewed 48

I have a JSON file that I want to store fields of it in a database. When those fields are at a first level, access to it is very simple. However, accessing different fields of the JSON file when they have 3 levels, the insert SQL script when making that call gives me an error. I have this piece of JSON file

"alexa": 282,
"pointsData": {
"2727": {
"id": "2727",
"needModeration": false,
"quoteDoc": "Help us ...",
"quoteEnd": 590,
"quoteStart": 200,
"quoteText": "We always use ...",
"services": [
"Amazon"
],

...

With the following SQL query to insert in a created table service, having those two attributes that I try to insert:

DECLARE @json NVARCHAR(max)  = N'{
    "alexa": 282,
    "pointsData": {
    "2727": {
    "id": "2727",
    "needModeration": false,
    "quoteDoc": "Help us ...",
    "quoteEnd": 590,
    "quoteStart": 200,
    "quoteText": "We always use ...",
    "services": [
    "Amazon"
    ]
    }
    }
 }';  

INSERT INTO SERVICE  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (
        pointsData.2727.id varchar(30), 
        pointsData.2727.services NVARCHAR(50)
       )

I get an error:

Incorrect syntax near '.2727'.

If I put it like [pointsData].[2727].[id] varchar(30), the error is:

Incorrect syntax near '.'.

To access the field "alexa": 282, this script with the alexa query varchar (30) does it perfectly

sql-server sql json
2 Answers

Try specifying the path in OPENJSON - note the double quotes on "2727" because it's numeric. Also don't forget that services is an array so you'lll need to give it an element index, e.g.:

DECLARE @json NVARCHAR(max)  = N'{
    "alexa": 282,
    "pointsData": {
    "2727": {
    "id": "2727",
    "needModeration": false,
    "quoteDoc": "Help us ...",
    "quoteEnd": 590,
    "quoteStart": 200,
    "quoteText": "We always use ...",
    "services": [
    "Amazon"
    ]
    }
    }
 }';  

INSERT INTO SERVICE  
  SELECT *   
  FROM OPENJSON(@json, '$.pointsData."2727"')  
  WITH (
        id varchar(30),
        services NVARCHAR(50) '$.services[0]'
       )

If you need the full content of services you'll need to extract it as JSON, e.g.:

DECLARE @json NVARCHAR(max)  = N'{
    "alexa": 282,
    "pointsData": {
    "2727": {
    "id": "2727",
    "needModeration": false,
    "quoteDoc": "Help us ...",
    "quoteEnd": 590,
    "quoteStart": 200,
    "quoteText": "We always use ...",
    "services": [
    "Amazon"
    ]
    }
    }
 }';  

INSERT INTO SERVICE  
  SELECT *   
  FROM OPENJSON(@json, '$.pointsData."2727"')  
  WITH (
        id varchar(30),
        services NVARCHAR(max) as JSON
       )
user_3901220

try this query. Note that all elements must be mapped exactly. I used id and services. If the names of the table fields SERVICE are something else, match it.

  1. I renamed column 2727 to mycol because it does not accept numeric names.
  2. I set the value to services nvarchar(max) because you have to use the Json return type for an array.
create table SERVICE
    (id int,
     services nvarchar(max),
     primary key(id))

   DECLARE @json NVARCHAR(max)  = N'{
      "alexa": 282,
      "pointsData": {
      "mycol": {
      "id": "2727",
      "needModeration": false,
      "quoteDoc": "Help us ...",
      "quoteEnd": 590,
      "quoteStart": 200,
      "quoteText": "We always use ...",
      "services": [
      "Amazon"
      ]
      }
      }
     }';  

INSERT INTO SERVICE (id, services)
SELECT id, services
FROM OPENJSON(@json)
WITH (id int '$.pointsData.mycol.id', services nvarchar(max) '$.pointsData.mycol.services' AS Json)
user_150195360
Related