CODEWITHSUNDEEP
jsonsql-serverformatting
Mahdi Tahsildari
Mahdi Tahsildari

Reputation: 13602

SQL query to return Json including array

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]"
    }
}}

  1. How can I add "type": "FeatureCollection", before the root?

  2. 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?

Upvotes: 1

Views: 5349

Answers (2)

Zhorov
Zhorov

Reputation: 30003

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
            ]
         }
      }
   ]
}

Upvotes: 3

VGorbach
VGorbach

Reputation: 41

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, ' ] }')

Upvotes: 0

Related Questions