Styjun

Cryptography and elliptic curves

Watching the game, having a puzzle

Is a vertical stabiliser needed for straight line flight in a glider?

Is there any evidence to support the claim that the United States was "suckered into WW1" by Zionists, made by Benjamin Freedman in his 1961 speech

Does the 500 feet falling cap apply per fall, or per turn?

Would encrypting a database protect against a compromised admin account?

Is ‘despite that’ right?

Electric kick drum pedal starts oscillating in such a way that it does not register hits

What food production methods would allow a metropolis like New York to become self sufficient

Is every story set in the future "science fiction"?

Removing all characters except digits from clipboard

Why are parallelograms defined as quadrilaterals? What term would encompass polygons with greater than two parallel pairs?

Has there been evidence of any other gods?

Names of the Six Tastes

Why are low spin tetrahedral complexes so rare?

Why was wildfire not used during the Battle of Winterfell?

Why is the Sun made of light elements only?

Company threw a surprise party for the CEO, 3 weeks later management says we have to pay for it, do I have to?

No such column 'DeveloperName' on entity 'RecordType' after Summer '19 release on sandbox

Why was the ancient one so hesitant to teach Dr Strange the art of sorcery

How to handle DM constantly stealing everything from sleeping characters?

Is it bad writing or bad story telling if first person narrative contains more information than the narrator knows?

Succinct and gender-neutral Russian word for "writer"

Is it a Munchausen Number?

Postgresql - return json array in select query instead of comma separated values

Alternate output format for psqlQuery for array elements inside JSON typePostgreSql Querying Array ValuesConverting comma separated string to integer array in PostgresQuerying for array containment against a JSON column in PostgreSQL 9.3PostgreSQL Select distinct values of comma separated values column excluding subsetsDynamically cast element to JSON array if it is a JSON string in PostgreSQLPostgreSQL JSON query returns NULLConvert comma separated id to comma separated string in postgresqlQuerying by array defined as JSON type

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I have the following query:

`SELECT * FROM reports
 WHERE id = $req.params.id`

the column type has the following value: "item1,item2,item3".

I want to return it as a JSON array: ["item1","item2","item3"]

I tried this:

`SELECT *, string_to_array(type, ',') AS type FROM reports
 WHERE id = $req.params.id`

but I still get it as a plain comma separated string.

Is it possible to do this or I have to convert it manually on the server and not on the query itself?

postgresql

share|improve this question

asked Mar 23 at 9:56

TheUnrealTheUnreal

7,3642990166

add a comment | 

0

I have the following query:

`SELECT * FROM reports
 WHERE id = $req.params.id`

the column type has the following value: "item1,item2,item3".

I want to return it as a JSON array: ["item1","item2","item3"]

I tried this:

`SELECT *, string_to_array(type, ',') AS type FROM reports
 WHERE id = $req.params.id`

but I still get it as a plain comma separated string.

Is it possible to do this or I have to convert it manually on the server and not on the query itself?

postgresql

share|improve this question

asked Mar 23 at 9:56

TheUnrealTheUnreal

7,3642990166

add a comment | 

I have the following query:

`SELECT * FROM reports
 WHERE id = $req.params.id`

the column type has the following value: "item1,item2,item3".

I want to return it as a JSON array: ["item1","item2","item3"]

I tried this:

`SELECT *, string_to_array(type, ',') AS type FROM reports
 WHERE id = $req.params.id`

but I still get it as a plain comma separated string.

Is it possible to do this or I have to convert it manually on the server and not on the query itself?

postgresql

share|improve this question

asked Mar 23 at 9:56

TheUnrealTheUnreal

7,3642990166

`SELECT * FROM reports
 WHERE id = $req.params.id`

`SELECT *, string_to_array(type, ',') AS type FROM reports
 WHERE id = $req.params.id`

share|improve this question

asked Mar 23 at 9:56

TheUnrealTheUnreal

7,3642990166

share|improve this question

asked Mar 23 at 9:56

TheUnrealTheUnreal

7,3642990166

asked Mar 23 at 9:56

TheUnrealTheUnreal

7,3642990166

asked Mar 23 at 9:56

TheUnrealTheUnreal

7,3642990166

1 Answer
1

active

oldest

votes

0

Pass the array as argument to to_jsonb():

SELECT to_jsonb(string_to_array(type, ',')) AS type
FROM reports

JSON support was introduced in Postgres 9.3 (json) and 9.4 (jsonb). In the older versions you can try to build a string representing a json array, e.g.:

with report(type) as (
 values ('item1,item2,item3')
)

select '[' || regexp_replace(type, '([^,]+)', '"1"', 'g') || ']' as type
from report

 type 
---------------------------
 ["item1","item2","item3"]
(1 row) 

share|improve this answer

edited Mar 23 at 11:54

answered Mar 23 at 10:08

klinklin

61.6k66093

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the equiviliant for older Postgres versions such as 8.4?
  
  – TheUnreal
  Mar 23 at 11:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TheUnreal - See the updated answer.
  
  – klin
  Mar 23 at 11:55
  

  
  
  
  

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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55312536%2fpostgresql-return-json-array-in-select-query-instead-of-comma-separated-values%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Pass the array as argument to to_jsonb():

SELECT to_jsonb(string_to_array(type, ',')) AS type
FROM reports

JSON support was introduced in Postgres 9.3 (json) and 9.4 (jsonb). In the older versions you can try to build a string representing a json array, e.g.:

with report(type) as (
 values ('item1,item2,item3')
)

select '[' || regexp_replace(type, '([^,]+)', '"1"', 'g') || ']' as type
from report

 type 
---------------------------
 ["item1","item2","item3"]
(1 row) 

share|improve this answer

edited Mar 23 at 11:54

answered Mar 23 at 10:08

klinklin

61.6k66093

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the equiviliant for older Postgres versions such as 8.4?
  
  – TheUnreal
  Mar 23 at 11:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TheUnreal - See the updated answer.
  
  – klin
  Mar 23 at 11:55
  

  
  
  
  

add a comment | 

0

Pass the array as argument to to_jsonb():

SELECT to_jsonb(string_to_array(type, ',')) AS type
FROM reports

JSON support was introduced in Postgres 9.3 (json) and 9.4 (jsonb). In the older versions you can try to build a string representing a json array, e.g.:

with report(type) as (
 values ('item1,item2,item3')
)

select '[' || regexp_replace(type, '([^,]+)', '"1"', 'g') || ']' as type
from report

 type 
---------------------------
 ["item1","item2","item3"]
(1 row) 

share|improve this answer

edited Mar 23 at 11:54

answered Mar 23 at 10:08

klinklin

61.6k66093

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the equiviliant for older Postgres versions such as 8.4?
  
  – TheUnreal
  Mar 23 at 11:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TheUnreal - See the updated answer.
  
  – klin
  Mar 23 at 11:55
  

  
  
  
  

add a comment | 

Pass the array as argument to to_jsonb():

SELECT to_jsonb(string_to_array(type, ',')) AS type
FROM reports

JSON support was introduced in Postgres 9.3 (json) and 9.4 (jsonb). In the older versions you can try to build a string representing a json array, e.g.:

with report(type) as (
 values ('item1,item2,item3')
)

select '[' || regexp_replace(type, '([^,]+)', '"1"', 'g') || ']' as type
from report

 type 
---------------------------
 ["item1","item2","item3"]
(1 row) 

share|improve this answer

edited Mar 23 at 11:54

answered Mar 23 at 10:08

klinklin

61.6k66093

SELECT to_jsonb(string_to_array(type, ',')) AS type
FROM reports

with report(type) as (
 values ('item1,item2,item3')
)

select '[' || regexp_replace(type, '([^,]+)', '"1"', 'g') || ']' as type
from report

 type 
---------------------------
 ["item1","item2","item3"]
(1 row) 

share|improve this answer

edited Mar 23 at 11:54

answered Mar 23 at 10:08

klinklin

61.6k66093

answered Mar 23 at 10:08

klinklin

61.6k66093

answered Mar 23 at 10:08

klinklin

61.6k66093