Postgres array_to_string() with array in JSONB field

Question

I want to get a TEXT of an array in JSONB field. The string that I expected are the following :

dom1.com,  dom2.com,  dom3.com

The JSONB field contains that :

{
    "id": 10,
    "domain_list": [
        "dom1.com",
        "dom2.com",
        "dom3.com"
    ]
}

I try to use the array_to_string() function by using the following code :

SELECT
    array_to_string(
        jsonb_array_elements(
            '{"id": 10,"domain_list": [  "dom1.com",  "dom2.com",  "dom3.com"]}'::jsonb->'domain_list'
        ),
        ', '
    );

But it returns the error

ERROR:  function array_to_string(jsonb, text) does not exist
LINE 2:         array_to_string(
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I also try this

SELECT string_agg(domain_list::text, ', ')
FROM json_array_elements(
    '{"id": 10,"domain_list": [  "dom1.com",  "dom2.com",  "dom3.com"]}'::jsonb->>'domain_list'
) as domain_list;

But it returns the error

ERROR:  function json_array_elements(text) does not exist
LINE 2:     FROM json_array_elements(
                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Michel Milezzi · Accepted Answer · 2018-03-23 14:33:51Z

2

You must use -> operator (return value as json) and proper function for you type, jsonb_array_elements in this case:

SELECT 
    string_agg(domain_list::text, ', ')
FROM jsonb_array_elements(
    '{"id": 10,"domain_list": [  "dom1.com",  "dom2.com",  "dom3.com"]}'::jsonb->'domain_list'
) as domain_list;

More info here.

answered Mar 23, 2018 at 14:33

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

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

2 Comments

Samuel Dauzon Over a year ago

Thank you. I forgotten to use -> in this case

Michel Milezzi Over a year ago

glad to help you @jedema

Collectives™ on Stack Overflow

Postgres array_to_string() with array in JSONB field

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related