JSON Select - Easily query values from JSON

  • Lest we (as DBA's) forget, MongoDB uses JSON. Move the parsing to an app server and store it as columns in tables in SQL Server of any version. For every SQL Server, there seems to be a plenitude of app servers (divide & conquer).

    Just my $0.02...

  • Joshua, does the library handle json escape characters like \\ or \/ and so on?

    I tried to use it with such string values in json elements and queries fail.

  • I wanted a more general approach to make available any JSON for SQL Server, so wrote a CLR to convert any JSON into an XML equivalent, parsing this XML inside of TSQL.

    Anybody interested ?

  • just wondering about the...

    "DateTimeOrdered": "2015-07-02 13:45:71"

    ...and the 71 ?

  • I'm really torn here. I want to give the article 5 stars (especially for a "first" article for the author) for how well it was written and I also want to give it 0 stars for the given story of it all, which could actually serve as a classic example of how NOT to go about developing an application. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I guess this one is self-confessing:

    alter table [Order]


    DateTimeOrdered as dbo.JsonDateTime([Json], 'DateTimeOrdered', 'yyyy-MM-dd HH:mm:ss') persisted


    create index IX_Order_DateTimeOrdered on [Order](DateTimeOrdered)

    What to add?

    Probably only this:

    Extract all the other values from b..y JSON (XML, whatever), store them in proper tables and use all the advantages of relational databases!

    Code for TallyGenerator

  • I got this error when installing.

    This functionality requires .NET Framework 3.5 SP1. Please install .NET Framework 3.5 SP1 to use this functionality..

    Error number: 6510 Severity: 16

    I actually only do v4.0 projects and above now. Is 3.5 a MSSQL requirement? Thanks.

Viewing 7 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply