Select query for jsonb column with list in postgresql-CodePudding

I have table with jsonb column. I need to write a query to select all rows for which the errors column contains LATEST_PERIOD_TOO_OLD. But the examples I found only work if you have a key. But I do not have it. Classic like cannot be used for jsonb either

Table:

 id |                 errors                 
---- ----------------------------------------------------------------
  1 | ["LATEST_PERIOD_TOO_OLD"]
  2 | ["LATEST_PERIOD_TOO_OLD", "DURATION_TOO_SMALL"]
  3 | null

Field in Entity class:

    @Type(type = "jsonb")
    @Column(name = "errors", columnDefinition = "jsonb")
    var errors: Set<ValidationError>? = null,

CodePudding user response：

The sql query is :

SELECT *
  FROM your_table
 WHERE jsonb_path_match(errors, '$[*] ? (exists(@ == "LATEST_PERIOD_TOO_OLD"))')

CodePudding user response：

Use @> contains operator.

select * from the_table where errors @> '["LATEST_PERIOD_TOO_OLD"]';

Unrelated to the question but why don't you use a normalized data design?