Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Query to get Value in row split with virgula character

I have the following table:

CREATE TABLE [dbo].[PRC_Tutors]
(
    [ID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [LivelloZoom] [int] NULL,
    [PosizioneGIS] [nvarchar](max) NULL,

    CONSTRAINT [PK_PRC_Tutors] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

This is the insert query:

INSERT INTO [dbo].[PRC_Tutors] ([ID], [UserID], [LivelloZoom], [PosizioneGIS])
VALUES (1, 1, 18, 'POLYGON((10.932815861897701 45.598233964590946,11.138809514241451 44.993985917715946,12.270401311116451 44.969266679434696,12.152298283772701 45.529569413809696,10.932815861897701 45.598233964590946))')

INSERT INTO [dbo].[PRC_Tutors] ([ID], [UserID], [LivelloZoom], [PosizioneGIS])
VALUES (2, 100, 10, 'POLYGON((12.053217932309531 44.2095244414735,12.261958166684531 44.14085989069225,12.341609045590781 44.280935574286,12.168574377622031 44.42650442194225,12.053217932309531 44.2095244414735))')

This is my query that returns a JSON:

SELECT  
    CAST((SELECT 
              'OK' As [status],
              TU.livelloZoom AS livelloZoom,
              (SELECT  REPLACE(REPLACE(N.PosizioneGIS, 'POLYGON((',''), '))', '') AS area
               FROM AA_V_PRC_Tutors N
               WHERE N.ID = TU.ID
               FOR JSON PATH, INCLUDE_NULL_VALUES) AS area
          FROM 
              AA_V_PRC_Tutors TU
          WHERE 
              UserID = @User_id
          FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) AS nvarchar(max))

This is the JSON output of this query:

{
    "status":"OK",
    "livelloZoom":18,
    "area":[
        {"area":"10.932815861897701 45.598233964590946,
        11.138809514241451 44.993985917715946,
        12.270401311116451 44.969266679434696,
        12.152298283772701 45.529569413809696,
        10.932815861897701 45.598233964590946"
        }
    ]
}

But I need to generate the following JSON:

{
    "status":"OK",
    "livelloZoom":18,
    "area":[
        [10.932815861897701, 45.598233964590946],
        [11.138809514241451, 44.993985917715946],
        [12.270401311116451, 44.969266679434696],
        [12.152298283772701, 45.529569413809696],
        [10.932815861897701, 45.598233964590946]
    ]
}

How can I get that JSON?

Tags:

2

Answers


  1. 0

    Firstly, you don’t need the AA_V_PRC_Tutors N reference, you can refer to the outer columns directly. You also don’t need th conversion to nvarchar(max) as that the default output of FOR JSON.

    SQL Server unfortunately doesn’t support JSON_AGG yet, which means we need to hack it with STRING_AGG and then prevent double-escaping using JSON_QUERY.

    So, to get your result, we need to convert your data to the actual geometry type (not sure why it wasn’t in the first place), then use STPointN to get each point. So we also need GENERATE_SERIES and STNumPoints to generate enough artifical rows.

    SELECT (
    SELECT
      'OK' As [status],
      TU.livelloZoom as livelloZoom,
      JSON_QUERY((   
          SELECT N'[' + STRING_AGG(
            CAST(CONCAT(N'[', v2.point.STX, N',', v2.point.STY, N']') AS nvarchar(max)),
            N','
          ) WITHIN GROUP(ORDER BY g.value) + N']'
          FROM (VALUES( CAST(PosizioneGIS AS geometry) )) AS v1(polygon)
          CROSS APPLY GENERATE_SERIES(1, v1.polygon.STNumPoints()) AS g
          CROSS APPLY (VALUES( v1.polygon.STPointN(g.value) )) AS v2(point)
      )) as area
      FROM PRC_Tutors TU
      WHERE UserID = @User_id
      FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
);

    db<>fiddle

    Given that you are using SQL Server 2016, you don’t have STRING_AGG so we need to use the old FOR XML PATH('') hack. And you don’t have GENERATE_SERIES so we need to use Itzik Ben-Gan’s cross-join generator.

    DECLARE @User_id int = 1;

WITH L0 AS (
    SELECT * FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) v(n)
),
L1 AS (
    SELECT 1 n FROM L0 a CROSS JOIN L0 b
)
SELECT (
    SELECT
      'OK' As [status],
      TU.livelloZoom as livelloZoom,
      JSON_QUERY(N'[' + STUFF((
          SELECT
            CAST(CONCAT(N',[', v2.point.STX, N',', v2.point.STY, N']') AS nvarchar(max))
          FROM (VALUES( CAST(PosizioneGIS AS geometry) )) AS v1(polygon)
          CROSS APPLY (
              SELECT TOP (v1.polygon.STNumPoints())
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS value
              FROM L1
          ) AS g
          CROSS APPLY (VALUES( v1.polygon.STPointN(g.value) )) AS v2(point)
          ORDER BY g.value
          FOR XML PATH('')
      ), 1, 1, N'') + N']') as area
      FROM PRC_Tutors TU
      WHERE UserID = @User_id
      FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
);

    db<>fiddle

    Login or Signup to reply.
  2. 0

    There are a few approaches to this problem:

    1. Parse the WKT coordinates down to individual elements using a combination of REPLACE() and nested STRING_SPLIT() operations and then rebuild them up into JSON arrays.
    2. Convert the WKT to a geography element and use the built-in geography methods to extract the coordinate components and rebuild them as JSON (as demonstrated in Charlieface’s answer – which may be the best approach).
    3. Use REPLACE() to directly convert the WKT into a JSON nested array syntax and include it in the results using JSON_QUERY().

    The below uses the third technique. The limitation is that the original data must precisely follow the expected format – no extra spaces or other variations.

    SELECT 
    'OK' As [status],
    TU.livelloZoom as livelloZoom,
    JSON_QUERY(C.CoordinatesNestedArray) as area
FROM PRC_Tutors TU
CROSS APPLY (
    -- From: 'POLYGON((X1 Y1,X2 Y2))' (with no extra spaces)
    -- To:   '[[X1,Y1],[X2,Y2]]'
    SELECT
        REPLACE(REPLACE(REPLACE(REPLACE(
            TU.PosizioneGIS,
            'POLYGON((', '[['), '))' ,']]'), ',' ,'],['), ' ', ',')
            AS CoordinatesNestedArray
) C
WHERE UserID = @User_id
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

    Sample Result (formatted using jsonformatter.org):

    {
  "status": "OK",
  "livelloZoom": 18,
  "area": [
    [
      10.932815861897701,
      45.598233964590946
    ],
    [
      11.138809514241451,
      44.993985917715946
    ],
    [
      12.270401311116451,
      44.969266679434696
    ],
    [
      12.152298283772701,
      45.529569413809696
    ],
    [
      10.932815861897701,
      45.598233964590946
    ]
  ]
}

    See this db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search