SQL에서 JSON으로 - SQL 2016에서 객체 배열에서 값 배열로

SQL에서 JSON으로 - SQL 2016에서 객체 배열에서 값 배열로

SQL 2016에는 SQL Server의 데이터를 JSON으로 변환하는 새로운 기능이 있습니다.객체 배열을 값 배열로 결합하는 데 어려움을 겪고 있습니다.

예 -

CREATE TABLE #temp (item_id VARCHAR(256))

INSERT INTO #temp VALUES ('1234'),('5678'),('7890')

SELECT * FROM #temp

--convert to JSON

SELECT (SELECT item_id 
FROM #temp
FOR JSON PATH,root('ids')) 

결과 -

{
    "ids": [{
        "item_id": "1234"
    },
    {
        "item_id": "5678"
    },
    {
        "item_id": "7890"
    }]
}

하지만 결과는...

"ids": [
        "1234",
        "5678",
        "7890"
    ]

누가 나 좀 도와줄래?

감사합니다! 저희가 찾은 영혼은 먼저 XML로 변환됩니다.

SELECT  
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + item_id + '"' 
FROM #temp FOR XML PATH('')),1,1,'') + ']' ) ids  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER 

마틴!

저는 이것이 훨씬 더 간단한 방법이라고 생각합니다.

    SELECT '"ids": ' + 
    REPLACE( 
      REPLACE( (SELECT item_id FROM #temp FOR JSON AUTO),'{"item_id":','' ),
      '"}','"' )

declare @temp table (item_id VARCHAR(256))

INSERT INTO @temp VALUES ('1234'),('5678'),('7890')

SELECT * FROM @temp

--convert to JSON

select 
    json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) as [json]
from @temp
for json path

문자열을 json 배열로 연결하는 경우:

1. 이스케이프 스트링 - STRING_ESCAPE

2. 콤마 구분자를 사용하여 문자열을 연결 - STRING_AGG, 콤마 ASCII 코드는 44입니다.

3. 괄호 안에 따옴표를 추가합니다. - 견적서 이름(파라미터 없음)

4. 문자열(요소 배열 포함)을 json으로 반환 - JSON_QUERY

원시값 배열은 유효한 JSON이므로 SQL Server의 JSON 기능에 원시값 배열 선택 기능이 포함되어 있지 않은 것이 이상합니다.(반대로 그러한 기능이 존재한다면, 적어도 꽤 검색해도 찾을 수 없습니다.)

위에서 개략적으로 설명한 접근방식은 다음과 같이 동작합니다.그러나 더 큰 쿼리의 필드에 적용하면 원시 요소의 배열이 따옴표로 둘러싸여 있습니다.

예: 이거

DECLARE @BomTable TABLE (ChildNumber dbo.udt_ConMetPartNumber);
INSERT INTO @BomTable (ChildNumber) VALUES (N'101026'), (N'101027');
SELECT N'"Children": ' + REPLACE(REPLACE((SELECT ChildNumber FROM @BomTable FOR JSON PATH), N'{"ChildNumber":', N''), '"}','');

다음 작업을 수행합니다.

"Children": ["101026,"101027]

그러나 위의 접근방식을 따르면 다음과 같습니다.

SELECT
    p.PartNumber,
    p.Description,
    REPLACE(REPLACE((SELECT
                        ChildNumber
                     FROM
                        Part.BillOfMaterials
                     WHERE
                        ParentNumber = p.PartNumber
                     ORDER BY
                        ChildNumber
                    FOR
                     JSON AUTO
                    ), N'{"ChildNumber":', N''), '"}', '"') AS [Children]
FROM
    Part.Parts AS p
WHERE
    p.PartNumber = N'104444'
FOR
    JSON PATH

작성:

[
    {
        "PartNumber": "104444",
        "Description": "ASSY HUB           R-SER  DRIV HP10  ABS",
        "Children": "[\"101026\",\"101027\",\"102291\",\"103430\",\"103705\",\"104103\"]"
    }
]

Children 배열을 문자열로 묶습니다.

이 버전(다른 버전 위에 빌드):

• 특수 JSON 문자(따옴표 등)를 올바르게 이스케이프합니다.
• 빈 배열을 반환합니다.[]데이터 없음

SQL 2017 이후 필요(이유)STRING_AGG):

    SELECT 
       CONCAT('[', 
            (SELECT STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', ',') 
             FROM #temp) 
        , ']')

여기 실용적일 수도 있고 아닐 수도 있는 엉뚱한 생각이 있다.데이터 세트를 반복하여 JSON 어레이에 추가:JSON_MODIFY:

with
  d (d) as (select * from (values (1),(2),(3),(4)) t (d)),
  j (d, j) as (
    -- Adapt the recursion to make it dynamic
    select 1, json_modify('[]', 'append $', d)
    from d
    where d = 1
    union all
    select d.d, json_modify(j, 'append $', d.d)
    from d join j on d.d = j.d + 1
  )
select * 
from j;

나는 설명을 위해 그것을 단순하게 했다.물론 역동적으로 만들 수 있습니다.그 결과, 다음과 같이 됩니다.

|d  |j        |
|---|---------|
|1  |[1]      |
|2  |[1,2]    |
|3  |[1,2,3]  |
|4  |[1,2,3,4]|

표준 SQL 에뮬레이션에도 사용 가능JSON_ARRAYAGG

이러한 솔루션의 대부분은 어레이의 내용을 나타내는 CSV를 작성한 후 CSV를 최종 JSON 형식으로 변환하는 것입니다.XML을 피하기 위해 사용하는 것은 다음과 같습니다.

DECLARE @tmp NVARCHAR(MAX) = ''

SELECT @tmp = @tmp + '"' + [item_id] + '",'
FROM #temp -- Defined and populated in the original question

SELECT [ids] = JSON_QUERY((
    SELECT CASE
        WHEN @tmp IS NULL THEN '[]'
        ELSE '[' + SUBSTRING(@tmp, 0, LEN(@tmp)) + ']'
        END
    ))
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

언급URL : https://stackoverflow.com/questions/37708638/sql-to-json-array-of-objects-to-array-of-values-in-sql-2016