SQL JSON - Concatenate multiple rows into a single JSON string
I'm trying to create the below JSON from a SQL query using FOR JSON feature.
"lineStrings": [
"[[[-0.340254,51.605946],[-0.340278,51.605685],[-0.339718,51.604400],
[-0.339280,51.603746],[-0.338915,51.603454],[-0.338657,51.603018]]]
Any ideas?
sql json
azure-sql-database asked Nov 23 '18 at 16:57
mac001mac001
133
add a comment |
I'm trying to create the below JSON from a SQL query using FOR JSON feature.
"lineStrings": [
"[[[-0.340254,51.605946],[-0.340278,51.605685],[-0.339718,51.604400],
[-0.339280,51.603746],[-0.338915,51.603454],[-0.338657,51.603018]]]
Any ideas?
sql json
azure-sql-database asked Nov 23 '18 at 16:57
mac001mac001
133
And your data currently looks like...
– Caius Jard
Nov 23 '18 at 17:01
From what query?
– Salman A
Nov 23 '18 at 18:10
add a comment |
I'm trying to create the below JSON from a SQL query using FOR JSON feature.
"lineStrings": [
"[[[-0.340254,51.605946],[-0.340278,51.605685],[-0.339718,51.604400],
[-0.339280,51.603746],[-0.338915,51.603454],[-0.338657,51.603018]]]
Any ideas?
sql json
azure-sql-database asked Nov 23 '18 at 16:57
mac001mac001
133
I'm trying to create the below JSON from a SQL query using FOR JSON feature.
"lineStrings": [
"[[[-0.340254,51.605946],[-0.340278,51.605685],[-0.339718,51.604400],
[-0.339280,51.603746],[-0.338915,51.603454],[-0.338657,51.603018]]]
Any ideas?
sql json
azure-sql-database sql json
azure-sql-database asked Nov 23 '18 at 16:57
mac001mac001
133
asked Nov 23 '18 at 16:57
mac001mac001
133
asked Nov 23 '18 at 16:57
mac001mac001
133
asked Nov 23 '18 at 16:57
mac001mac001
133
asked Nov 23 '18 at 16:57
mac001mac001
133
133
And your data currently looks like...
– Caius Jard
Nov 23 '18 at 17:01
From what query?
– Salman A
Nov 23 '18 at 18:10
add a comment |
And your data currently looks like...
– Caius Jard
Nov 23 '18 at 17:01
From what query?
– Salman A
Nov 23 '18 at 18:10
And your data currently looks like...
– Caius Jard
Nov 23 '18 at 17:01
And your data currently looks like...
– Caius Jard
Nov 23 '18 at 17:01
From what query?
– Salman A
Nov 23 '18 at 18:10
From what query?
– Salman A
Nov 23 '18 at 18:10
add a comment |
1 Answer
1
active
oldest
votes
Update
Please try this / let me know if it's what you're after. Example
declare @myTable table (a decimal(12,8), b decimal(12,8))
insert @myTable (a, b)
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
select string_agg(c,',') as lineStrings
from
(
select 1 ignore, JSON_MODIFY(JSON_MODIFY('', 'append $', a), 'append $', b)
from @myTable
) x(ignore, c)
group by ignore
for json path, without_array_wrapper
ps. There's some useful examples in the docs for some common issues.
Original Attempt
Here's a hacky solution which uses the standard for json auto output, then manipulates the resulting string using the replace function. Since you're working only with numbers it's safe, but I wouldn't risk it if you had any text fields.
There's likely a better way, but I'm not sure what that is...
select replace(replace(replace(replace(jsonString,'"a":',''),'"b":',''),'{','['),'}',']') hackedJson
, jsonString returnedJson
from
(
select *
from
(
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
) myTable (a, b)
FOR JSON AUTO
) j( jsonString)
answered Nov 23 '18 at 17:26
JohnLBevanJohnLBevan
14.5k146108
1
Thank you John, perfect!
– mac001
Nov 28 '18 at 16:48
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53450494%2fsql-json-concatenate-multiple-rows-into-a-single-json-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Update
Please try this / let me know if it's what you're after. Example
declare @myTable table (a decimal(12,8), b decimal(12,8))
insert @myTable (a, b)
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
select string_agg(c,',') as lineStrings
from
(
select 1 ignore, JSON_MODIFY(JSON_MODIFY('', 'append $', a), 'append $', b)
from @myTable
) x(ignore, c)
group by ignore
for json path, without_array_wrapper
ps. There's some useful examples in the docs for some common issues.
Original Attempt
Here's a hacky solution which uses the standard for json auto output, then manipulates the resulting string using the replace function. Since you're working only with numbers it's safe, but I wouldn't risk it if you had any text fields.
There's likely a better way, but I'm not sure what that is...
select replace(replace(replace(replace(jsonString,'"a":',''),'"b":',''),'{','['),'}',']') hackedJson
, jsonString returnedJson
from
(
select *
from
(
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
) myTable (a, b)
FOR JSON AUTO
) j( jsonString)
answered Nov 23 '18 at 17:26
JohnLBevanJohnLBevan
14.5k146108
1
Thank you John, perfect!
– mac001
Nov 28 '18 at 16:48
add a comment |
Update
Please try this / let me know if it's what you're after. Example
declare @myTable table (a decimal(12,8), b decimal(12,8))
insert @myTable (a, b)
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
select string_agg(c,',') as lineStrings
from
(
select 1 ignore, JSON_MODIFY(JSON_MODIFY('', 'append $', a), 'append $', b)
from @myTable
) x(ignore, c)
group by ignore
for json path, without_array_wrapper
ps. There's some useful examples in the docs for some common issues.
Original Attempt
Here's a hacky solution which uses the standard for json auto output, then manipulates the resulting string using the replace function. Since you're working only with numbers it's safe, but I wouldn't risk it if you had any text fields.
There's likely a better way, but I'm not sure what that is...
select replace(replace(replace(replace(jsonString,'"a":',''),'"b":',''),'{','['),'}',']') hackedJson
, jsonString returnedJson
from
(
select *
from
(
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
) myTable (a, b)
FOR JSON AUTO
) j( jsonString)
answered Nov 23 '18 at 17:26
JohnLBevanJohnLBevan
14.5k146108
1
Thank you John, perfect!
– mac001
Nov 28 '18 at 16:48
add a comment |
Update
Please try this / let me know if it's what you're after. Example
declare @myTable table (a decimal(12,8), b decimal(12,8))
insert @myTable (a, b)
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
select string_agg(c,',') as lineStrings
from
(
select 1 ignore, JSON_MODIFY(JSON_MODIFY('', 'append $', a), 'append $', b)
from @myTable
) x(ignore, c)
group by ignore
for json path, without_array_wrapper
ps. There's some useful examples in the docs for some common issues.
Original Attempt
Here's a hacky solution which uses the standard for json auto output, then manipulates the resulting string using the replace function. Since you're working only with numbers it's safe, but I wouldn't risk it if you had any text fields.
There's likely a better way, but I'm not sure what that is...
select replace(replace(replace(replace(jsonString,'"a":',''),'"b":',''),'{','['),'}',']') hackedJson
, jsonString returnedJson
from
(
select *
from
(
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
) myTable (a, b)
FOR JSON AUTO
) j( jsonString)
answered Nov 23 '18 at 17:26
JohnLBevanJohnLBevan
14.5k146108
Update
Please try this / let me know if it's what you're after. Example
declare @myTable table (a decimal(12,8), b decimal(12,8))
insert @myTable (a, b)
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
select string_agg(c,',') as lineStrings
from
(
select 1 ignore, JSON_MODIFY(JSON_MODIFY('', 'append $', a), 'append $', b)
from @myTable
) x(ignore, c)
group by ignore
for json path, without_array_wrapper
ps. There's some useful examples in the docs for some common issues.
Original Attempt
Here's a hacky solution which uses the standard for json auto output, then manipulates the resulting string using the replace function. Since you're working only with numbers it's safe, but I wouldn't risk it if you had any text fields.
There's likely a better way, but I'm not sure what that is...
select replace(replace(replace(replace(jsonString,'"a":',''),'"b":',''),'{','['),'}',']') hackedJson
, jsonString returnedJson
from
(
select *
from
(
values (-0.340254,51.605946),(-0.340278,51.605685),(-0.339718,51.604400),(-0.339280,51.603746),(-0.338915,51.603454),(-0.338657,51.603018)
) myTable (a, b)
FOR JSON AUTO
) j( jsonString)
answered Nov 23 '18 at 17:26
JohnLBevanJohnLBevan
14.5k146108
edited Nov 23 '18 at 18:05
answered Nov 23 '18 at 17:26
JohnLBevanJohnLBevan
14.5k146108
answered Nov 23 '18 at 17:26
JohnLBevanJohnLBevan
14.5k146108
answered Nov 23 '18 at 17:26
JohnLBevanJohnLBevan
14.5k146108
14.5k146108
1
Thank you John, perfect!
– mac001
Nov 28 '18 at 16:48
add a comment |
1
Thank you John, perfect!
– mac001
Nov 28 '18 at 16:48
1
1
Thank you John, perfect!
– mac001
Nov 28 '18 at 16:48
Thank you John, perfect!
– mac001
Nov 28 '18 at 16:48
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53450494%2fsql-json-concatenate-multiple-rows-into-a-single-json-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
And your data currently looks like...
– Caius Jard
Nov 23 '18 at 17:01
From what query?
– Salman A
Nov 23 '18 at 18:10