Coffeehouse Thread

5 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL Server 2016 and JSON support

Back to Forum: Coffeehouse
  • User profile image
    figuerres

     

     

    So I have some json and i need to get at bits of it in tsql to return rows of data to SSRS which can't read json.

    i have an onject that has a collection and i need to pull out the nth sub object from a collection where the index will chnage from call to call.

    i can get something like this to work:

     

    Select value
    from openjson( @ManifestJson , N'$.Stops[2].Packages' )

    but i can't seem to make the path a variabale , it seems to only work with a literal / constant string!

    declare @jkey nvarchar(2000)

    set @jkey = '''$.Stops['+ convert(nvarchar(10), @StopNumber-1)+ '].Packages'''

    Select value
    from openjson( @ManifestJson , @jkey )

     

    gives a syntax  error!

    anyone know how to make that work for me ??

     

     

     

     

     

  • User profile image
    TheTraveler
  • User profile image
    figuerres

    in this case i do not think so.

    i need a *VERY* specific report layout for the company.

    but if power bi can make other general business reports it may be good for other cases.

    by the way i did find other syntax for tsql json that looks like it will do the trick.

    need to come back here later and post some info on what i did....

  • User profile image
    cheong

    For now, your best bet is to write a little CLR UDF that wraps NewtonSoft.JSON 

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • User profile image
    figuerres

    For now, your best bet is to write a little CLR UDF that wraps NewtonSoft.JSON 

    i thought about that , but as i am getting the hang of how to use the support functions  i am finding that i can get it with the built in functions.

    json_value() , json_query()   openjson() and "with (  name type 'path')"

    are working very well for me now...

    also use of [key] =  is abit of a trick to learn,some places it's easy others not so much.

    if you have  a collection  of objects [key] can be used to pick the row you need, if you have an object [key] can select the "column" that looks like a row in sql.

    and you can combine queries so that openjson , select value  becomes the input to an outer openjson()  query.

    so  i now have a query that returns a set of rows that makes one call to fetch the json data and uses nested calls to pick out which member object of which collection.

     

     

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.