Can you directly query a db table for JSON content

user_8899635 0
Viewed 79

Unable to extract useful info from JSON data stored in table.

I have JSON data stored in a SQL Server table which I would like to access one or two elements to check values etc. My table is defined as below:

CREATE TABLE [Events].[events](
    [eventID] [int] IDENTITY(1,1) NOT NULL,
    [eventType] [smallint] NOT NULL,
    [eventDate] [datetime] NOT NULL,
    [eventMetaData] [nvarchar](max) NULL,
    CONSTRAINT [PK_events] PRIMARY KEY CLUSTERED 
    (
        [eventID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

with the JSON data being held in the eventMetaData col.

The eventMetaData column stores the data as follows:

[{
    "jn": "jn1",
    "src": "client123",
    "id": 649
}]

and I'd like to try to extract this info for display on a page.

So far I've tried to use the JSON_VALUE and JSON_QUERY methods of extracting the required string although I believe its the JSON_VALUE one I should be using. 

SELECT top 10 eventId, eventDate, 
 JSON_VALUE(eventData,'$.jn') AS jn
from events
WHERE ISJSON(eventData)>0
order by eventdate desc

When I run this I get the eventID & eventDate but always get NULL in the jn col.

Some posts have shown examples of the data being in a variable of type nvarchar(max), and if I put my JSON into this it works with my query but I really need it to come from the table as I need to display multiple rows to my customer.

From the example I posted above I'd like to get the eventID, eventDate & jn values output from the query but at the minute I'm getting the eventID & eventDate but always get NULL in the jn col.

Ultimately I'm hoping to have more keys within the JSON col which I can access and display to the end user.

sql-server json
2 Answers

I can't get JSON_VALUE working, either, but OPENJSON works for me:

DECLARE @j NVARCHAR(255) = 
    '[{
        "jn": "jn1",
        "src": "client123",
        "id": 649
    }]'

SELECT [jn]
     , [src]
     , [id]
FROM OPENJSON(@j)
WITH (jn NVARCHAR(10) '$.jn'
    , src NVARCHAR(255) '$.src'
    , id INT '$.id'
    )
user_12385530

You are very close. If your eventMetaData is always a JSON array with one element, you may use JSON_VALUE like this:

Table:

CREATE TABLE [events] (
    [eventID] [int] IDENTITY(1,1) NOT NULL,
    [eventType] [smallint] NOT NULL,
    [eventDate] [datetime] NOT NULL,
    [eventMetaData] [nvarchar](max) NULL
)

INSERT INTO [events] 
    (eventType, eventDate, eventMetaData)
VALUES 
    (1, '20190618', N'[{"jn": "jn1","src": "client123","id": 649}]')

T-SQL:

SELECT
   JSON_VALUE(eventMetaData, '$[0].jn') jn,
   JSON_VALUE(eventMetaData, '$[0].src') src,
   JSON_VALUE(eventMetaData, '$[0].id') id,
   *
FROM [events]

Output:

jn  src         id  eventID eventType   eventDate           eventMetaData
jn1 client123   649 1       1           18/06/2019 00:00:00 [{"jn": "jn1","src": "client123","id": 649}]

Notes:

If there are more items in your JSON array, use OPENJSON with CROSS APPLY operator:

CREATE TABLE [events] (
    [eventID] [int] IDENTITY(1,1) NOT NULL,
    [eventType] [smallint] NOT NULL,
    [eventDate] [datetime] NOT NULL,
    [eventMetaData] [nvarchar](max) NULL
)

INSERT INTO [events] 
    (eventType, eventDate, eventMetaData)
VALUES 
    (1, '20190618', N'[{"jn": "jn1","src": "client123","id": 649}, {"jn": "jn2","src": "client124","id": 659}]')

SELECT
   j.jn,
   j.src,
   j.id,
   e.*
FROM [events] e
CROSS APPLY OPENJSON(e.eventMetaData) WITH (
   jn nvarchar(3) '$.jn', 
   src nvarchar(50) '$.src', 
   id int '$.id'
) j
user_65780800
Related