Search code examples
jsonpostgresqlentity-attribute-value

Convert an EAV table to JSON update query

I have a simple EAV table, that I want to convert to JSON/B and insert it into a column that I will add to the entity table.

This is meant to be used as a migration query.

My EAV :

Record ( id, ... ) RecInfos ( recordid, key, value )

For earch entry in the record table, it will create a json representation of each key / value that is can be found in the RecInfos table, and this will be send as an update on the Record table.

I am using postgresql 10.3

Solution

  • Here is what I was searching for :

    update
    record r
set
    infos = (
                select
                        json_agg(json_build_object('name',i.name,'value',i.value))
                from
                        recinfos i
                where
                        i.rec_id = r.id
            )