How do I access a JSON object's fields in SQL?

Question

I pulled JSON data from a third-party API and I am trying to automate its entry into an SQL database. The format of the JSON is as follows :

{ "fields": {
    "Example.FirstName": "Bob",
    "Example.LastName": "Test",
    "Example.Salary": "$50000"
    },
    "UserId": "1001231234"
}

I can retrieve the UserId, but anything within fields I am not sure how to access. I figured it would just be fields.Example.FirstName but I keep getting null. This is what I have done

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Python38\Data.JSON', SINGLE_CLOB) as import
SELECT * FROM OPENJSON (@JSON)
WITH
(
    [fields.Example.FirstName] varchar(50),
    [UserId] varchar(50)
)

If anyone can explain the syntax of how to access the data in fields, it would be much appreciated. I am using Microsoft SQL Server if that helps.

Could you please clearify. Would you like to add a JSON to a Microsoft SQL Server DB? Or would you like to store a JSON in Microsoft SQL Server DB and want to know how to access values from it? — Samuel Wahlberg
– Samuel Wahlberg, Commented Oct 26, 2020 at 14:11
I would like to store JSON data in Microsoft SQL Server (automatically) so I'm in the process of creating a stored procedure to do that. I need to access the values in the fields object to do so. So for example, with how its written now, I'll get [fields.Example.FirstName] = null, but [UserId] will give me a row for each id in the JSON file. — rashidcs
– rashidcs, Commented Oct 26, 2020 at 14:17

GMB · Accepted Answer · 2020-10-26 14:24:09Z

2

I think you want:

select * 
from openjson (@json)
with (
    firstname varchar(50) '$.fields."Example.FirstName"',
    lastname  varchar(50) '$.fields."Example.LastName"',
    salary    varchar(50) '$.fields."Example.Salary"',
    UserId    varchar(50)
)

answered Oct 26, 2020 at 14:24

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

rashidcs Over a year ago

This is exactly what I was looking for. Thank you so much!

Collectives™ on Stack Overflow

How do I access a JSON object's fields in SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related