i have a PostgreSQL table containing a column with key value pairs ("KeyValuePairs" of text) separated by a semicolon character:
| Id | Name | KeyValuePairs |
|---|---|---|
| 1 | A | Key1:Value1;Key2:Value2;Key3:Value3 |
| 2 | B | Key10:Value10;Key11:Value11 |
| 3 | C | Key20:Value20;Key21:Value21;Key22:Value22;Key23:Value23;Key24:Value24 |
How do i convert the text of KeyValuePairs into a JSON syntax (still of type text, not JSON) using a SQL query? The expected result is:
| Id | Name | KeyValuePairs |
|---|---|---|
| 1 | A | { "Key1": "Value1", "Key2": "Value2", "Key3": "Value3" } |
| 2 | B | { "Key10": "Value10", "Key11": "Value11" |
| 3 | C | etc. |
CodePudding user response：
You can replace the delimiters
select Id,Name, '{"' || replace(replace(KeyValuePairs, ':', '":"'),';','","') || '"}' KeyValuePairs
from yourtable
CodePudding user response：
Use Json Object and parse that string into json
JObject json = JObject.Parse(str);
You might want to refer to Json.NET documentation