Работаем с JSON в SQL Server 2016

[Listrigon]

Вещь прикольненькая, уже даже запользовали на живом сервере. Переписали для теста пару хитрых WebAPI-шных методов с Entity Framework, на хранимки с JSON,
Показалось удобно, на входе JSON и на выходе JSON, по суди сам метод стал одной строчкой кода по вызову хранимки, да и время выполнения уменьшилась в 2-3 раза.

[AlanDenton]

Если отдавать JSON, то это возможно и является хорошим новшеством SQL Server 2016, но вот проблемы при хранении JSON обеспечены точно (особенно в сравнении с XML который сжимается изначально).

Индексов создать на JSON нельзя (по аналогии с selective xml indexes которые доступны с SQL Server 2012 SP1) и возможно только вычисляемые поля выход из положения…

DECLARE @json NVARCHAR(MAX) = N'{
    "Orders":
    [
        {
        "Order": {
            "Number": "S043659",
            "Date": "2011-05-31T00:00:00"
        },
        "Account": "Microsoft",
        "Item": {
            "Price": 59.99,
            "Quantity": 1
        }
        },
        {
            "Order": {
                "Number": "S043661",
                "Date": "2011-06-01T00:00:00"
            },
            "Account": "Nokia",
            "Item": {
                "Price": 24.99,
                "Quantity": 3
            }
        }
    ]
}'

DECLARE @xml XML = N'
<Orders>
    <Order Number="S043659" Date="2011-05-31T00:00:00">
        <Account>Microsoft</Account>
        <Item>
            <Price>59.99</Price>
            <Quantity>1</Quantity>
        </Item>
    </Order>
    <Order Number="S043661" Date="2011-06-01T00:00:00">
        <Account>Nokia</Account>
        <Item>
            <Price>24.99</Price>
            <Quantity>3</Quantity>
        </Item>
    </Order>
</Orders>'

SELECT [@json] = DATALENGTH(@json)
     , [@xml] = DATALENGTH(@xml)

@json     @xml
--------- -----------
1132      362

[app25app]

Если убрать все лишние знаки из JSON, то разница становиться не такая кретичная

DECLARE @json NVARCHAR(MAX) = N'{"Orders":[{"Order":{"Number":"S043659","Date":"2011-05-31T00:00:00"},"Account":"Microsoft","Item":{"Price":59.99,"Quantity":1}},{"Order":{"Number":"S043661","Date":"2011-06-01T00:00:00"},"Account":"Nokia","Item":{"Price":24.99,"Quantity":3}}]}'

DECLARE @xml XML = N'<Orders><Order Number="S043659" Date="2011-05-31T00:00:00"><Account>Microsoft</Account><Item><Price>59.99</Price><Quantity>1</Quantity></Item></Order><Order Number="S043661" Date="2011-06-01T00:00:00"><Account>Nokia</Account><Item><Price>24.99</Price><Quantity>3</Quantity></Item></Order></Orders>'
SELECT [@json] = DATALENGTH(@json)
     , [@xml] = DATALENGTH(@xml)

[Don_Eric]

Entity Framework как-то поддерживает это?

[Listrigon]

А что именно вы хотите от Entity Framework, в базе это же просто строка. Просто появился инструмент для работы со строкой как с JSON объектом.

[dmitry_h]

Под поддержкой имеется ввиду что хотелось бы чтобы EF воспринимал json как структуру и умел транслировать запросы на выборку на подобие XPATH. Например есть таблица User(id int, jsonData nvarchar(max)). В jsonData лежит все о пользователе { «name»: ...., «disabled»: false,… }. И мы пишем запросы которые опираются на эту структуру (dc.Users.Where(u => u.jsonData.getValue(«disabled») == «true»)). А не просто на строку. Но конечно же EF этого не умеет, все так же, как и с ограниченной поддержкой XML.

[kxl]

Ждем JSONB… а также GIN и Vodka :)

[dmitry_h]

Интересно протестировать быстродействие на выборке в 1 млн записей. С XML было все печально, запросы выжирали все ресурсы. Из за этого в свое время и съехали на PostgreSQL чтобы иметь возможность полноценно работать с иерархиями на уровне 1 записи.
MS SQL очень мощная СУБД, но к сожалению поддержка JSON так и осталась не сильной его стороной. Вообще раньше было множество сторонних скриптов, которые позволяли делать то же что и эти функции (например https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/), хорошо что теперь из коробки будет работать. Лично я очень надеялся что появится более нативная поддержка в виде аналога jsonb оптимизированных для выборок.