SQL query to return Json including array

Question

I have a relational database (SQL Server) with normal data.

I am trying to build something with this format:

{
"type": "FeatureCollection",
"features": [
    { 
        "type": "Feature", 
        "id": "1", 
        "properties": { "address": "2" }, 
        "geometry": { 
            "type": "Point", 
            "coordinates": [36.33456, 59.523456] 
        } 
    },
    { 
        "type": "Feature", 
        "id": "2", 
        "properties": { "address": "151" }, 
        "geometry": { 
            "type": "Point", 
            "coordinates": [36.33456, 59.523456] 
        }
    }]
}

So far I have written this query:

select top 10 
    'Feature' as [type], 
    m.Id as id, m.Address as 'properties.address',
    'Point' as 'geometry.type',
    '[' + m.location + ']' as 'geometry.coordinates'
from 
    Buildings m
where 
    m.Location is not null 
    and m.Location <> ''
for json path, root('features')

But what I receive in action is like:

{
"features": [{
    "type": "Feature",
    "id": 250343,
    "properties": {
        "address": "there"
    },
    "geometry": {
        "type": "Point",
        "coordinates": "[5714843008,3363769468.235179]"
    }
}, {
    "type": "Feature",
    "id": 266306,
    "properties": {
        "address": "here"
    },
    "geometry": {
        "type": "Point",
        "coordinates": "[36.38449104993326,59.48238372802735]"
    }
}}

How can I add "type": "FeatureCollection", before the root?
I want the coordinate part to hold an array of 2 numbers, but in my current code it is a string holding the array. How can I achieve an array?

Zhorov · Accepted Answer · 2020-06-13 15:55:10Z

3

It's difficult without test data, but I think that you can build the expected JSОN output using the following statement. You need one more FOR JSON PATH (to generate the outer JSON object) and a JSON_QUERY call (to return a JSON array of scalar values instead of text holding the array):

Table:

CREATE TABLE Buildings (
   Id int,
   Address varchar(100),
   Location varchar(100)
)
INSERT INTO Buildings (Id, Address, Location)
VALUES
   (250343, 'there', '5714843008,3363769468.235179'),
   (266306, 'here', '36.38449104993326,59.48238372802735')

Statement:

SELECT 
   [type] = 'FeatureCollection',
   [features] = JSON_QUERY((
      select top 10 
          'Feature' as [type], 
          m.Id as id, m.Address as 'properties.address',
          'Point' as 'geometry.type',
          JSON_QUERY('[' + m.location + ']') as 'geometry.coordinates'
      from 
          Buildings m
      where 
          m.Location is not null 
          and m.Location <> ''
      for json path   
   ))
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Result:

{
   "type":"FeatureCollection",
   "features":[
      {
         "type":"Feature",
         "id":250343,
         "properties":{
            "address":"there"
         },
         "geometry":{
            "type":"Point",
            "coordinates":[
               5714843008,
               3363769468.235179
            ]
         }
      },
      {
         "type":"Feature",
         "id":266306,
         "properties":{
            "address":"here"
         },
         "geometry":{
            "type":"Point",
            "coordinates":[
               36.38449104993326,
               59.48238372802735
            ]
         }
      }
   ]
}

edited Jun 13, 2020 at 15:55

answered Jun 10, 2020 at 19:06

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mahdi Tahsildari Over a year ago

I had tested this before asking my question, and if I remember correctly, I got an error like 'incorrect syntax near keyword select ...' now either it is not possible or the syntax is not correct.

VGorbach · Accepted Answer · 2020-06-10 21:12:54Z

I figured a way to build custom JSON using STUFF (GROUP_CONCAT in MySQL) and XML. See if this works for you:

1 Answer to question 1 - Note, this is a TWO-STEP PROCESS:

a) VARIABLE TO HOLD QUERY DATA
DECLARE @QUERY_DATA varchar(8000)
b) CUSTOM QUERY TO FETCH QUERY DATA FORMATTED FOR INNER JSON METADATA (all in one line)
SET @QUERY_DATA = (
SELECT STUFF(
(
SELECT ',' + CONCAT('{"type": "Feature","id": ', m.Id, ',"properties": {"address": "', m.Address, '"},"geometry": {"type": "Point","coordinates": [', m.location, ']}}'
)
FROM
Buildings m
WHERE
m.Location is not null
and m.Location <> ''
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') +)
c) FINAL OUTPUT - Concatenate / Join your JSON
SELECT CONCAT('{ ''type'':''FeatureCollection''','','',',''FEATURES'': [ ', @QUERY_DATA, ' ] }')

2 - ANSWER TO QUESTION 2 with CONSOLIDATED solution using separated X and Y coordinates as two INTEGER Type elements

{''X'':', CAST(LEFT( m.location, CHARINDEX(',', m.location)-1) as int), ',''Y'':', cast(RIGHT( m.location, LEN( m.location)-CHARINDEX(',', m.location)) as int),'}

Therefore, when we add this together, your new query would look like the following:

a) VARIABLE TO HOLD QUERY DATA +DECLARE @QUERY_DATA varchar(8000)
b) CUSTOM QUERY TO FETCH QUERY DATA FORMATTED FOR INNER JSON METADATA (all in one line), including splitting coordinates into two INTEGER Type elements
SET @QUERY_DATA = (
SELECT STUFF(
(
SELECT ',' + CONCAT('{"type": "Feature","id": ', m.Id, ',"properties": {"address": "', m.Address, '"},"geometry": {"type": "Point","coordinates": [ {''X'':', CAST(LEFT( m.location, CHARINDEX(',', m.location)-1) as int), ',''Y'':', cast(RIGHT( m.location, LEN( m.location)-CHARINDEX(',', m.location)) as int),'}]}}'
)
FROM
Buildings m
WHERE
m.Location is not null
and m.Location <> ''
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') +)
c) FINAL OUTPUT - Concatenate / Join your OUTER and INNER JSON
SELECT CONCAT('{ ''type'':''FeatureCollection''','','',',''FEATURES'': [ ', @QUERY_DATA, ' ] }')

Collectives™ on Stack Overflow

SQL query to return Json including array

2 Answers 2

1 Comment

1 Answer to question 1 - Note, this is a TWO-STEP PROCESS:

2 - ANSWER TO QUESTION 2 with CONSOLIDATED solution using separated X and Y coordinates as two INTEGER Type elements

Therefore, when we add this together, your new query would look like the following:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Answer to question 1 - Note, this is a TWO-STEP PROCESS:

2 - ANSWER TO QUESTION 2 with CONSOLIDATED solution using separated X and Y coordinates as two INTEGER Type elements

Therefore, when we add this together, your new query would look like the following:

Comments

Your Answer

Sign up or log in

Post as a guest

Related