Return Parts of an Array in Postgres

Question

I have a column (text) in my Postgres DB (v.10) with a JSON format. As far as i now it's has an array format.

Here is an fiddle example: Fiddle

If table1 = persons and change_type = create then i only want to return the name and firstname concatenated as one field and clear the rest of the text.

Output should be like this:

id  table1  did execution_date  change_type   attr      context_data
1   Persons 1   2021-01-01      Create        Name      [["+","name","Leon Bill"]]
1   Persons 2   2021-01-01      Update        Firt_name [["+","cur_nr","12345"],["+","art_cd","1"],["+","name","Leon"],["+","versand_art",null],["+","email",null],["+","firstname","Bill"],["+","code_cd",null]]
1   Users   3   2021-01-01      Create        Street    [["+","cur_nr","12345"],["+","art_cd","1"],["+","name","Leon"],["+","versand_art",null],["+","email",null],["+","firstname","Bill"],["+","code_cd",null]]

Tomáš Záluský · Accepted Answer · 2021-11-19 15:01:29Z

Disassemble json array into SETOF using json_array_elements function, then assemble it back into structure you want.

select m.*
     , case
         when m.table1 = 'Persons' and m.change_type = 'Create'
           then (
             select '[["+","name",' || to_json(string_agg(a.value->>2,' ' order by a.value->>1 desc))::text || ']]'
             from json_array_elements(m.context_data::json) a
             where a.value->>1 in ('name','firstname')
           )
         else m.context_data
       end as context_data
from mutations m

modified fiddle

(Note:

utilization of alphabetical ordering of names of required fields is little bit dirty, explicit order by case could improve readability
resulting json is assembled from string literals as much as possible since you didn't specified if "+" should be taken from any of original array elements
the to_json()::text is just for safety against injection

)

Collectives™ on Stack Overflow

Return Parts of an Array in Postgres

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