Question 90375 in PostgreSQL Question posted in
The official documentation can be found here.

How to extract text from jsonb array in PostgreSQL

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

Id. ErrorCode.
101 ["exit code: 1"]
102 ["exit code: 3"]
103 ["OOMKILLED"]

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'

Id. Error
101 exit code: 1
102 exit code: 3
103 OOMKILLED
Tags:

3

Answers


  1. 0

    try this :

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

    For more info about json functions see the manual

    Login or Signup to reply.
  2. 0

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

    select * from t where err ? 'OOMKILLED';

    https://sqlize.online/s/eW

    Login or Signup to reply.
  3. 0

    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');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search