CODEWITHSUNDEEP
jsonpostgresql
Wells
Wells

Reputation: 10969

postgres & jsonb, list extraction (to table)

I have the following list stored in a JSON object in a jsonb table:

[
  {
    "id": 123,
    "p": 1
  },
  {
    "id": 456,
    "p": 2
  },
  {
    "id": 789,
    "p": 3
  },
  {
    "id": 012,
    "p": 4
  },
  {
    "id": 345,
    "p": 5
  },
  {
    "id": 678,
    "p": 6
  },
  {
    "id": 901,
    "p": 7
  },
  {
    "id": 234,
    "p": 8
  },
  {
    "id": 567,
    "p": 9
  },
  {
    "id": 890,
    "p": 10
  },
  {
    "id": 1234,
    "p": 11
  },
  {
    "id": 5678,
    "p": 12
  },
  {
    "id": 9012,
    "p": 13
  }
]

I want a table with id and p columns, here having 13 rows.

Upvotes: 0

Views: 63

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

Here is one option using json_array_elements:

SELECT
    json_array_elements(data)->>'id' AS id,
    json_array_elements(data)->>'p' AS p
FROM yourTable;

Demo

Upvotes: 1

Related Questions