Prepend String Using CONCAT/CONCAT_WS with STRING_AGG results if STRING_AGG is NOT Empty or NULL?

Question

I have JSONB data that is stored in the data column like so:

...
"dealers": [
    { "dealership":"LHM"},
    { "dealership”:"Camp"},
    { "dealership":"HMA"}
  ],

"cars": [
    { "name":"Ford"},
    { "name":"BMW" },
    { "name":"Fiat"}
  ],
...

There are several more attributes which store several more arrays of objects, but for the sake of simplifying my question I only wanted to mention these two.

Firstly, I wanted to see if I could collect all of the name attributes from each object into one field. I found the following question and accepted answer which got me to my initial goal doing something similar to the following:

SELECT
    id
  , STRING_AGG(car->>'name', ', ')
FROM
  autos,
  json_array_elements(autos.data->'cars') as car
GROUP BY 1

This results in a single field that looks like this:

Ford, BMW, Fiat

My end goal now is to be able to concatenate all of the other STRING_AGG object attributes from MULTIPLE arrays of objects into one field prepended with a brief description of each, but ONLY if the result of STRING_AGG is NOT EMPTY and NOT NULL (Most important in my case is NOT EMPTY (''). For example, it should look like this:

...//Cars: Ford, BMW, Fiat //Dealerships: LMH, Camp, HMA //... // etc.

I have tried to use CONCAT and CONCAT_WS, and tried utilizing NULLIF but am not sure how to achieve what I am looking for. Particularly, if it is possible to prepend a description string to each STRING_AGG result. Can you assist?

UPDATE: I was able to figure out how to do this with case statements inside of a CONCAT, but I am now wondering if that is a good way to do this or if this can be done in a similar way to S-Man’s given answer. Thank you.

S-Man · Accepted Answer · 2020-04-09 09:58:49Z

1

Click: step-by-step demo:db<>fiddle

WITH autos AS (
    SELECT '{"cars": [{"name": "BMW", "year": 1991}, {"name": "Ford", "model": "Mustang"}, {"name": "Fiat"}, {"name": "VW", "model": "Golf", "year": 2000}, {"name": "VW", "model": ""}]}'::jsonb as data
)
SELECT 
    'Cars: ' || STRING_AGG(NULLIF(car ->> 'name', ''), ',') || ' // '
    'Years: ' || STRING_AGG(NULLIF(car ->> 'year', ''), ',') || ' // '
    'Models: ' || STRING_AGG(NULLIF(car ->> 'model', ''), ',')
FROM autos,
    jsonb_array_elements(data->'cars') as car

Using this data set:

{
    "cars": [
        {
            "name": "BMW",
            "year": 1991
        },
        {
            "name": "Ford",
            "model": "Mustang"
        },
        {
            "name": "Fiat"
        },
        {
            "name": "VW",
            "year": 2000,
            "model": "Golf"
        },
        {
            "name": "VW",
            "model": ""
        }
    ]
}

You can see: no model (== null) for the BMW, no year for the Ford, no data at all for the Fiat, all attributes at the VW Golf, an empty model for the second VW.

First get a table for all data:

SELECT 
    car ->> 'name' as name,
    car ->> 'year' as year,
    car ->> 'model' as model
FROM autos,
    jsonb_array_elements(data->'cars') as car

You already used the jsonb_array_elements() function, which expands a JSON array. The ->> operator gives the value of the attributes (in this case of each array element) as text.

Now, you could normalize the empty values (model of the second VW, in this case) to NULL using NULLIF(). You could make a value to NULL if it is empty:

NULLIF(myvalue, '')

This would lead you to:

SELECT 
    NULLIF(car ->> 'name', '') as name,
    NULLIF(car ->> 'year', '') as year,
    NULLIF(car ->> 'model', '') as model
FROM autos,
    jsonb_array_elements(data->'cars') as car

Now you have no empty values anymore, but many NULL values.

Now, you could use STRING_AGG() to aggregate all values as you expect. The clue is, STRING_AGG() automatically ignores NULL values, so you don't need to hesitate.

SELECT 
    STRING_AGG(NULLIF(car ->> 'name', ''), ',') as names,
    STRING_AGG(NULLIF(car ->> 'year', ''), ',') as years,
    STRING_AGG(NULLIF(car ->> 'model', ''), ',') as models
FROM autos,
    jsonb_array_elements(data->'cars') as car

Now, you only have three columns and one row. Every value is aggregated into on string.

Finally you want to chain them into one string. For this you could use CONCAT() or the operator ||:

SELECT 
    'Cars: ' || STRING_AGG(NULLIF(car ->> 'name', ''), ',') || ' // '
    'Years: ' || STRING_AGG(NULLIF(car ->> 'year', ''), ',') || ' // '
    'Models: ' || STRING_AGG(NULLIF(car ->> 'model', ''), ',')
FROM autos,
    jsonb_array_elements(data->'cars') as car

Edit:

Clarified data:

{
    "cars": [
        {
            "name": "BMW"
        },
        {
            "name": "Ford"
        },
        {
            "name": "Fiat"
        }
    ],
    "dealers": [
        {
            "dealership": "LHM"
        },
        {
            "dealership": "Camp"
        },
        {
            "dealership": "HMA"
        }
    ]
}

Click: demo:db<>fiddle

SELECT
    'Cars: ' || s1.names || ' // Dealers: ' || s2.dealerships
FROM
    autos, 
    LATERAL (SELECT string_agg(c ->> 'name', ',') as names FROM jsonb_array_elements(data->'cars') as c) s1,
    LATERAL (SELECT string_agg(d ->> 'dealership', ',') as dealerships FROM jsonb_array_elements(data->'dealers') as d) s2

In that case, you have to expand and aggregate your arrays before joining. Please add the NULLIF() function at the relevant columns as done above.

edited Apr 9, 2020 at 9:58

answered Apr 7, 2020 at 12:09

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

5 Comments

finiteloop Over a year ago

I did end up figuring this out today, and greatly appreciate your help. I think I should have explained my question a bit better though. Your way is really handy, but what if you have multiple arrays of objects that you are wanting to get concatenated into a single field, similar to how you have done with your chaining method in your answer? Is that doable in a similar way?

S-Man Over a year ago

Please provide an example (for this please edit your question)

finiteloop Over a year ago

no problem. I updated my question. Is that more clear, or are you needing additional clarity/detail?

S-Man Over a year ago

In that case you have to expand and merge the arrays before joining. According to dba.stackexchange.com/a/54289/161799 this can be done this way: dbfiddle.uk/…; Edited the answer

finiteloop Over a year ago

Thank you for taking the time to work this out. I am marking your answer as correct. I ended up doing something similar to what you told me about but a little bit different. Ultimately, your answer and follow up suggestion helped! Thank you.

Collectives™ on Stack Overflow

Prepend String Using CONCAT/CONCAT_WS with STRING_AGG results if STRING_AGG is NOT Empty or NULL?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related