0

Need

FIELD like this, with ' and without , at the end of whole field.

{name:'color',value:'red'},{name:'width',value:'300 ml'}

Tried

string_agg(distinct '{name:\'' || param_name || '\',value:\'' || value || '\'},','') as "FIELD"

but error behind 2nd \

SQL Error [42601]: ERROR: syntax error at or near "\"

With , at the end of whole field, I don't know how to do that.

Thanks.

2
  • Have a look here.
    – Vérace
    Jul 31 '19 at 10:12
  • In case you are trying to generate a JSON value: are you aware that the value you are generating is invalid JSON? Jul 31 '19 at 11:26
1

to escape a ' in a string in postgres, you have to double it

List of escape sequences for Postgresql string data types

so this give you this :

select string_agg(distinct '{name:''' || name || ''',value:''' || value || '''}',',') as "FIELD"

I tested it, it works =)

However, i don't know why you want to create this field, but it's probably better to follow a_horse_with_no_name's answer and use Jsonb_agg and look around Json manipulation in Postgres

https://www.postgresql.org/docs/current/functions-json.html

1
  • It works, thanks. :) And what about comma at the end of whole field?
    – genderbee
    Jul 31 '19 at 9:58
3

If you want to generate JSON, it is easier to use JSON functions for that, e.g.:

string_agg(distinct jsonb_build_object('name', param_name, 'value', value)::text, ',')

Or create a proper JSON array of the result:

jsonb_agg(distinct jsonb_build_object('name', param_name, 'value', value))

Online example: https://rextester.com/EPOX97933

2
  • @a_horse_with_no_names Hi, I think better solution is to have it in simply one sql command.
    – genderbee
    Jul 31 '19 at 11:39
  • 1
    @genderbee: that is one SQL command - just two alternatives Jul 31 '19 at 11:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.