0

I have some data in a jsonb col that I need to clean and insert into another text array column.

What I have:

[
  {
    "v": "birthday"
  },
  {
    "v": "reference"
  }
]

What I want (_text):

{birthday, reference}

My query:

select array[col -> 0 ->> 'v', col-> 1 ->> 'v'] from src

gives the expected result as above.

However, when the value is null, I want the value not to be inserted i.e. no NULL value nor empty:

[
  {
    "v": "birthday"
  }
]

should result in

{birthday}

and not

{birthday, [NULL]} -- NULL value
{birthday,} -- Empty string with coalesce

How do you conditionally insert an element into an array when creating one, based on the value of the element?

1 Answer 1

-1

array_remove() seems to answer my use case (by removing in a 2nd step the null values):

select array_remove(array[col -> 0 ->> 'v', col-> 1 ->> 'v'], null) from src
2
  • 1
    ca you elaborate your answer.
    – nbk
    Feb 5, 2021 at 10:43
  • I have updated my answer
    – mich
    Feb 8, 2021 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.