I want to be able to extract text from jsonb array in a new column as text. This is my SQL table.

This is my column definition '[]'::jsonb

I needed help understanding which select command I could use in that case. I used the query above but with no success.

Select reasons -> ' ' as TTT from my_table

I want to get these results after a select command., so I can do SQL filters like where error = 'exit code: 1' or where error = 'OOMKILLED'

CodePudding user response：

Use ->> to extract the first array element as text:

Select id, reasons ->> 0 as reason
from my_table
where reasons ->> 0 in ('exit code: 1','OOMKILLED');

If you don't want to repeat the expression use a derived table:

select * 
from (
  select id, reasons ->> 0 as reason
  from my_table
) 
where reason in ('exit code: 1','OOMKILLED');

CodePudding user response：

try this :

SELECT Id, jsonb_array_elements_text(ErrorCode :: jsonb) AS Error
  FROM my_table

For more info about json functions see the manual

CodePudding user response：

When you need check is JSONB array contains some value, just use ? operator:

select * from t where err ? 'OOMKILLED';

https://sqlize.online/s/eW