postgresql: automated extracting strings from text

Question

I've the following table in a postgresl database

        id  | species                                                                           
        ----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 |[{"id":1,"animalName":"Lupo appennico","animalCode":"LUPO"},{"id":2,"animalName":"Orso bruno marsicano","animalCode":"ORSO"},{"id":3,"animalName":"Volpe","animalCode":"VOLPE"}]
        ----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
          2 |[{"id":1,"animalName":"Cinghiale","animalCode":"CINGHIALE"},{"id":2,"animalName":"Orso bruno marsicano","animalCode":"ORSO"},{"id":3,"animalName":"Cervo","animalCode":"CERVO"}]|

I would like to extract only values after '"animalName":' and put them in a new field.

        id  | new_field                                  |
        ----+--------------------------------------------+
          1 |Lupo appennico, Orso bruno marsicano,Volpe  |
        ----+--------------------------------------------+
          2 |Cinghiale, Orso bruno marsicano, Cervo      |

Unfortunately the field is a text type (not json or array). I've tried with regexp without success.

GMB · Accepted Answer · 2020-04-10 13:38:10Z

1

Your column is not of a json datatype, but it seems to contain valid json. If so, you can cast it and use json functions on it:

select id, string_agg(j ->> 'animalName', ', ') new_field
from mytable t
cross join lateral jsonb_array_elements(t.species::jsonb) j(obj)
group by id
order by id

Demo on DB Fiddle:

id | new_field                                  
-: | :------------------------------------------
 1 | Lupo appennico, Orso bruno marsicano, Volpe
 2 | Cinghiale, Orso bruno marsicano, Cervo

answered Apr 10, 2020 at 13:38

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql: automated extracting strings from text

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related