Extracting JSON Values Longer Than 4000 Characters

Published on: 2018-09-18

A while back I built an automated process that parses JSON strings into a relational format.

Up until recently this process had been working great: my output table had all of the data I was expecting, neatly parsed into the correct rows and columns.

Last week I noticed an error in the output table however.  One row that was supposed to have a nicely parsed JSON value for a particular column had an ugly NULL instead.

Truncated?

First I checked my source JSON string – it had the “FiveThousandAs” property I was looking for:

So the source data was fine.

I checked the table column I was inserting into as well and confirmed it was defined as nvarchar(max), so no problem there.

The last thing I checked was the query I was using:

If I run that on it’s own, I reproduce the NULL I was seeing inserted into my table:

JSON_VALUE is limiting

After a little bit more research, I discovered that the return type for JSON_VALUE is limited to 4000 characters.   Since JSON_VALUE is in lax mode by default, if the output has more than 4000 characters, it fails silently.

To force an error in future code I could use SELECT JSON_VALUE(@json, 'strict $.FiveThousandAs')  so at least I would be notified immediately of an problem with my  query/data (via failure).

Although strict mode will notify me of issues sooner, it still doesn’t help me extract all of the data from my JSON property.

(Side note: I couldn’t define my nvarchar(max) column as NOT NULL because for some rows the value could be NULL, but in the future I might consider adding additional database validation with a check constraint).

OPENJSON

The solution to reading the entire 5000 character value from my JSON property is to use OPENJSON:

My insert query needed to be slightly refactored, but now I’m able to return any length value (as long as it’s under 2gb).

In hindsight, I should have used OPENJSON() from the start: not only is it capable of parsing the full length values from JSON strings, but it performs significantly faster than any of the other SQL Server JSON functions.

As a best practice, I think I’m going to use OPENJSON by default for any JSON queries to avoid problems like this in the future.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

6 thoughts on “Extracting JSON Values Longer Than 4000 Characters”

  1. Hello,
    I tried your code but it seems not working:

    DECLARE @json nvarchar(max) = N'{
    “version”: 0.6,
    “generator”: “Overpass API 0.7.55.4 3079d8ea”,
    “osm3s”: {
    “timestamp_osm_base”: “2018-10-04T10:07:02Z”,
    “timestamp_areas_base”: “2018-10-04T09:02:02Z”,
    “copyright”: “The data included in this document is from http://www.openstreetmap.org. The data is made available under ODbL.”
    },
    “elements”: [
    {
    “type”: “node”,
    “id”: 501591237,
    “lat”: 49.4943882,
    “lon”: -117.2910343,
    “tags”: {
    “amenity”: “cinema”,
    “name”: “Civic Theatre”,
    “website”: “http://civictheatre.ca”
    }
    },
    {
    “type”: “node”,
    “id”: 2293276829,
    “lat”: -41.2709865,
    “lon”: 173.2842196,
    “tags”: {
    “amenity”: “cinema”,
    “name”: “Cinema 6”
    }
    }
    ]
    }’;

    SELECT *
    FROM OPENJSON(@json)
    WITH (elements nvarchar(max) ‘$.elements’)

  2. I found a solution here: https://stackoverflow.com/questions/37218254/sql-server-openjson-read-nested-json using CROSS APPLY.

    select
    elements.type, elements.id, elements.lat, elements.lon, elements.tags
    from openjson (@json)
    with
    (
    version nvarchar(10),
    generator nvarchar(1000),
    osm3s nvarchar(1000),
    elements nvarchar(max) as json
    )
    as Projects
    cross apply openjson (Projects.elements)
    with
    (
    type char(10),
    id numeric,
    lat decimal(9, 6),
    lon decimal(9, 6),
    tags nvarchar(max) as json
    ) as elements

    Are we sure that this is the only way to parse the JSON?
    Is as ugly as it can be…

  3. Sorry Bert, I was panicking for nothing….

    SELECT * FROM OPENJSON(@json, N’$.elements’)
    WITH (
    [type] nvarchar(max) N’$.type’ ,
    [id] nvarchar(max) N’$.id’,
    [lat] nvarchar(max) N’$.lat’,
    [lon] nvarchar(max) N’$.lon’,
    [amenity] nvarchar(max) N’$.tags.amenity’,
    [name] nvarchar(max) N’$.tags.name’

    )

    All good now…

  4. FYI,

    I wrote a query that can help you make REST call directly from SSMS and save the data into SQL Server:

    https://stackoverflow.com/questions/52645781/ssms-openjson-works-but-not-with-direct-rest-call/52686738#52686738

    The code is simply horrible but it will be helpful in case someone need to create a stored procedure that makes a REST call.

    …your post made me OBSESSED, 🙂 , I just wanted to find a solution as we live in a World where JSON is mostly on internet and not on your computer hard drive.

    Thank you again and keep up the good videos.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.