I normally use a script with this structure to insert rows in a postgres table.

select 'my_table' as name,
    '{"my_table":{' ||
    string_agg('"' || nr || code || '":{"code":"' || nr || code || '","name_eng":"' || eng || '","name_de":"' || de || '","name_se":"' || se || '","nr_cd":"' || nr || '","code":"' || code || '"}', ',') || '}}' as records,
    '[{"attribut":"nr_cd","codetable_type":"String"},{"attribut":"cus_immopaccode","codetable_type":"String"}]' as custom_attributes
from (select '01' as code, 'Rat' as eng, 'Ratte' as de, 'Ratta' se 
        union all select  '02', 'Cow','Kuh','Ko'
        union all select  '03', 'Dog','Hund','Hund'
        union all select  '04', 'Cat','Katze','Katt'
        

    )d
        cross join (select '1' as nr 
                union all select '2'
                union all select '3'
                )m  

Until now they datatypes has been name(text),records(text),custom_attributes(text) but now the records column has been changed to jsonb.

Therefor I need to rewrite the query but im not good in json and when I try building it with jsonb_build_object and jsonb_object_agg and don't manage to get it right.

Maybe there is some helpful soul out there that can help me and save me some hours work? :)

Thanks in advance.

CodePudding user response：

Example for you:

select 
json_build_object (
    'table', 
    json_object_agg (
        nr || code, 
        json_build_object (
            'Code', nr || code, 'name_eng', eng, 'name_de', de, 'name_se', se, 'nr_cd', nr, 'code', code
        )
    )
)
from (
    select '01' as code, 'Rat' as eng, 'Ratte' as de, 'Ratta' se 
    union all 
    select  '02', 'Cow','Kuh','Ko'
    union all 
    select  '03', 'Dog','Hund','Hund'
    union all 
    select  '04', 'Cat','Katze','Katt'
)d
cross join (
    select '1' as nr 
    union all 
    select '2'
    union all 
    select '3'
)m  

Result:

{
    "table": {
        "101": {
            "Code": "101",
            "name_eng": "Rat",
            "name_de": "Ratte",
            "name_se": "Ratta",
            "nr_cd": "1",
            "code": "01"
        },
        "201": {
            "Code": "201",
            "name_eng": "Rat",
            "name_de": "Ratte",
            "name_se": "Ratta",
            "nr_cd": "2",
            "code": "01"
        },
        "301": {
            "Code": "301",
            "name_eng": "Rat",
            "name_de": "Ratte",
            "name_se": "Ratta",
            "nr_cd": "3",
            "code": "01"
        },
        "102": {
            "Code": "102",
            "name_eng": "Cow",
            "name_de": "Kuh",
            "name_se": "Ko",
            "nr_cd": "1",
            "code": "02"
        },
        "202": {
            "Code": "202",
            "name_eng": "Cow",
            "name_de": "Kuh",
            "name_se": "Ko",
            "nr_cd": "2",
            "code": "02"
        },
        "302": {
            "Code": "302",
            "name_eng": "Cow",
            "name_de": "Kuh",
            "name_se": "Ko",
            "nr_cd": "3",
            "code": "02"
        },
        "103": {
            "Code": "103",
            "name_eng": "Dog",
            "name_de": "Hund",
            "name_se": "Hund",
            "nr_cd": "1",
            "code": "03"
        },
        "203": {
            "Code": "203",
            "name_eng": "Dog",
            "name_de": "Hund",
            "name_se": "Hund",
            "nr_cd": "2",
            "code": "03"
        },
        "303": {
            "Code": "303",
            "name_eng": "Dog",
            "name_de": "Hund",
            "name_se": "Hund",
            "nr_cd": "3",
            "code": "03"
        },
        "104": {
            "Code": "104",
            "name_eng": "Cat",
            "name_de": "Katze",
            "name_se": "Katt",
            "nr_cd": "1",
            "code": "04"
        },
        "204": {
            "Code": "204",
            "name_eng": "Cat",
            "name_de": "Katze",
            "name_se": "Katt",
            "nr_cd": "2",
            "code": "04"
        },
        "304": {
            "Code": "304",
            "name_eng": "Cat",
            "name_de": "Katze",
            "name_se": "Katt",
            "nr_cd": "3",
            "code": "04"
        }
    }
}