CODEWITHSUNDEEP
postgresqljsonbjsonbuilder
user2210516
user2210516

Reputation: 683

Postgres. string_agg text column into json

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.

Upvotes: 0

Views: 316

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3303

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"
        }
    }
}

Upvotes: 1

Related Questions