Postgres string manipulation

Question

I have a field in my table that contains a string that can follow any of 3 formats:

null
"[string]"
"Following fields matched: {string=string, string=string, string=string..}"

For 1 I need output : null For 2 I need output : For 3 I need to split each pair into its each row with columns [key] & [value]

Now, I have solved this challenge, I just think that I have done it in the most eloquent or efficient manner.

Would be grateful if anyone could point me to a better solution ? Thanks

SELECT 
  h.nid, 
  trim(d.key) as "key", 
  trim(d.value) as "value"
FROM 
  (
          SELECT 
              r.*,
              cast(
                  replace(replace(replace(replace(
                        case 
                            when left(r.relation_details,25) = 'Following fields matched:' 
                            then right(r.relation_details, char_length(r.relation_details)-25)
                            when left(r.relation_details,1) = '[' 
                            then '{' || r.relation_details || '=' || r.relation_details || '}'
                            else null 
                        end 
                        ,'=' ,'":"'),',' ,'","'),'{' ,'{"'),'}' ,'"}')
              as json) as json
            FROM podium_core.pd_entity_relation r
  ) h
  JOIN 
  json_each_text(h.json) d ON true

I suggest pre-processing your data outside of Postgres using something like Notepad++, then import it later. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 14, 2021 at 15:14
Thanks but the data is from a 3rd party app based on postgres. I cannot change the source data. — SimonB
– SimonB, Commented Jun 14, 2021 at 15:32

nachospiu · Accepted Answer · 2021-06-14 17:01:33Z

You query is not working properly to me (with data example '{a=b, c=d, e=f}' I get an empty row).

My attempt, I am not sure that my query result is exactly what you want.

I convert all rows to array format, then unnest them (using comma separator). I split rows that contain equal sign to array, and then I get the result in two columns (key, value).

Table:

CREATE TABLE pd_entity_relation (
    relation_details character varying(100)
);

Input (example data):

INSERT INTO pd_entity_relation VALUES (null);
INSERT INTO pd_entity_relation VALUES ('[x]');
INSERT INTO pd_entity_relation VALUES ('{a=b, c=d, e=f}');

Query:

SELECT 
    '[' || f[1] || ']' AS key,
    '[' || CASE WHEN cardinality(f) > 1 THEN f[2] ELSE f[1] END || ']' AS value
 
FROM (
    SELECT regexp_split_to_array(UNNEST(CASE WHEN relation_details IS NULL THEN ARRAY[NULL] ELSE replace(replace(relation_details, '[', '{'), ']', '}')::text[] END), '=') AS f
    FROM pd_entity_relation
) AS sq

Output:

|  key | value |
|:----:|:-----:|
| null | null  |
| [x]  | [x]   |
| [a]  | [b]   |
| [c]  | [d]   |
| [e]  | [f]   |

That's brilliant, thank you :) The third insert should be 'Following fields matched:{a=b, c=d, e=f}' , but I've now accounted for that now and your code works brilliantly. I shall try to provide better source data statements in future ;)
You are welcome @SimonB! My mistake: I haven't read carefully your query. Check my query with more data to be sure it works correctly.

Collectives™ on Stack Overflow

Postgres string manipulation

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related