Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос к файлу JSON  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Добрый день,

Есть примерно такая структура (я ее упростил и уменьшил):

DECLARE @json NVARCHAR(MAX)
SET @json = 
N'{
    "id": 1,
    "code": "XYZ",
    "name": "Literacy",
    "studentAssessmentId": 999,
    "studentId": 12345,
    "studentName": "John, Smith",
    "userId": 1234,
    "systemTime": "2019-09-26T02:39:15.587+0000",
    "lodgementId": 456,
    "aspects": [{
            "id": 7097,
            "aspectId": 185,
            "aspectName": "WRITING",
            "value": {
                "studentName": "John, Smith",
                "studentSRN": "TESTSRN",
                "aspectValue": "2"
            },
            "seqNo": 2,
            "score": null,
            "assessmentItems": [{
                    "id": 9007,
                    "assessmentItemId": 3,
                    "itemName": "Q1",
                    "value": "3"
                }, {
                    "id": null,
                    "assessmentItemId": 10,
                    "itemName": "E1",
                    "value": null
                }
            ]
        }, {
            "id": 12679,
            "aspectId": 11,
            "aspectName": "READING",
            "value": {
                "studentName": "John, Smith",
                "studentSRN": "TESTSRN",
                "aspectValue": "0"
            },
            "seqNo": 11,
            "score": null,
            "assessmentItems": [{
                    "id": null,
                    "assessmentItemId": 12,
                    "itemName": "E2",
                    "value": null
                }, {
                    "id": 9706,
                    "assessmentItemId": 13,
                    "itemName": "Q2",
                    "value": "0"
                }, {
                    "id": 15148,
                    "assessmentItemId": 18,
                    "itemName": "Q3",
                    "value": "0"
                }
            ]
        }
    ],
    "rollClass": {
        "id": 385,
        "schoolCode": 104,
        "className": "XY",
        "students": [{
                "id": null,
                "srn": "TESTSRN",
                "firstName": "John",
                "lastName": "Smith"
            }
        ]
    }
}'


select ISJSON(@json)



Мне нужно построить запрос, который вытащит все элементы нижнего уровня (assessmentItems), где itemName не начинается на "E", к ним добавит столбцы из уровня выше (aspects) и самого верхнего уровня, а также вытащит некоторые столбцы из ноды: rollClass

Что-то вроде этого:
SELECT *  FROM OPENJSON(@json)  
  WITH (id bigint '$.aspects.assessmentItems.id',
        studentAssessmentId bigint '$.studentAssessmentId',  -- from the root
        student_id bigint '$.studentId',  -- from the root
        aspectName varchar(128) '$.aspects.aspectName',  -- from the parent
        itemName varchar(128) '$.aspects.assessmentItems.itemName',  
        itemvalue varchar(128) '$.aspects.assessmentItems.value',  
		rollClass varchar(128) '$.rollClass.className')  


Только этот запрос возвращает одну записть, а должен 3: для Q1, Q2 и Q3.

Спасибо.
26 сен 19, 11:35    [21979588]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к файлу JSON  [new]
invm
Member

Откуда: Москва
Сообщений: 8797
select
 json_value(a.value, '$.id') as id,
 json_value(b.value, '$.assessmentItemId') as assessmentItemId
from
 openjson(@json, '$.aspects') a cross apply
 openjson(a.value, '$.assessmentItems') b;

До нужного вида допилите самостоятельно.
26 сен 19, 12:03    [21979624]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к файлу JSON  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
invm
select
 json_value(a.value, '$.id') as id,
 json_value(b.value, '$.assessmentItemId') as assessmentItemId
from
 openjson(@json, '$.aspects') a cross apply
 openjson(a.value, '$.assessmentItems') b;

До нужного вида допилите самостоятельно.


Спасибо, а можно ли к запросу из JSON добавить столбец с функцией? Что-то вроде такого:

SELECT *  FROM OPENJSON(@json)  
  WITH (school_id bigint '$.schoolCode', 
	   student_id int '$.studentId',
  	  [dbo].[fn_pkey](student_id int '$.studentId', 15000, 1, 1))  
27 сен 19, 09:53    [21980530]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к файлу JSON  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
Roust_m
а можно ли к запросу из JSON добавить столбец с функцией? Что-то вроде такого:

SELECT *  FROM OPENJSON(@json)  
  WITH (school_id bigint '$.schoolCode', 
	   student_id int '$.studentId',
  	  [dbo].[fn_pkey](student_id int '$.studentId', 15000, 1, 1))  
Зачем, можно же написать
SELECT ..., [dbo].[fn_pkey](student_id, 15000, 1, 1)
27 сен 19, 09:59    [21980533]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к файлу JSON  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
invm
select
 json_value(a.value, '$.id') as id,
 json_value(b.value, '$.assessmentItemId') as assessmentItemId
from
 openjson(@json, '$.aspects') a cross apply
 openjson(a.value, '$.assessmentItems') b;

До нужного вида допилите самостоятельно.


Спасибо, а как можно из верхнего уровня в этот запрос вытащить столбец, например studentId. Что-то вроде такого:

select
 json_value(a.value, '$.id') as id,
 json_value(b.value, '$.assessmentItemId') as assessmentItemIdб
  json_value(с.value, '$.studentId') as studentId,
from
 openjson(@json, '$.aspects') a cross apply
 openjson(a.value, '$.assessmentItems') b cross apply
 openjson(@json, '$') c;
3 окт 19, 05:17    [21985508]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к файлу JSON  [new]
invm
Member

Откуда: Москва
Сообщений: 8797
Roust_m
а как можно из верхнего уровня в этот запрос вытащить столбец
select
 json_value(@json, '$.studentId') as studentId,
 json_value(a.value, '$.id') as id,
 json_value(b.value, '$.assessmentItemId') as assessmentItemId
from
 openjson(@json, '$.aspects') a cross apply
 openjson(a.value, '$.assessmentItems') b;
3 окт 19, 10:13    [21985586]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить