Access the same Josn field which are inside {} curly brackers under the same column in postgresql-CodePudding

I want to query the element locktype and the workspaceID from within the different curly brackets fields under the column named value,

For example:

[
  {
    "score":0.0,
    "locktype":1,
    "rank":1,
    "workspaceId":"4R5"
  },
  {
    "score":0.0,
    "lockType":2,
    "rank":2,
    "workspaceId":"6yt"
  }
]

The result should be like,

Locktype	workspaceID
1	4R5
2	6yt

The table name is Custom.

Any help would be highly appreciated. Thanks.

CodePudding user response：

According Postgres document You can use json_array_elements and cross join.

Demo

select
  ej.value ->> 'locktype' as locktype,
  ej.value ->> 'workspaceId' as workspaceId
from
  test t
  cross join json_array_elements(t.value::json) ej