Postgres get search and get multiple array json to each row

Question

I wants to get all subscriptions with interval "1 WEEK" from the following 'data' column

[
  {
    "id": "tran_6ac25129951962e99f28fa488993",
    "amount": 1200,
    "client": {
      "id": "client_622bdf4cce2351f28243",
      "subscription": [
        {
          "id": "sub_a67d59efb2bcbf73485a",
          "amount": 3900,
          "currency": "USD",
          "interval": "1 WEEK"
        },
        {
          "id": "sub_a67d59efb2bcbf73485a",
          "amount": 3900,
          "currency": "USD",
          "interval": "1 WEEK"
        }
      ]
    },
    "currency": "USD"
  },
  {
    "id": "tran_xxxxxxx",
    "amount": 1200,
    "client": {
      "id": "client_xxxxxx8243",
      "subscription": [
        {
          "id": "sub_xxefb2bcbf73485a",
          "amount": 3900,
          "currency": "USD",
          "interval": "1 Year"
        },
        {
          "id": "sub_yyyyyb2bcbf73485a",
          "amount": 3900,
          "currency": "USD",
          "interval": "1 WEEK"
        }
      ]
    },
    "currency": "USD"
  }
]

My table structure:

CREATE TABLE transactions
(
  data json,
  id bigserial NOT NULL,
  created_date time without time zone,
  CONSTRAINT transactions_pkey PRIMARY KEY (id)
)

In output I wants to get all "1 WEEk" subscription as rows. Above data should give 3 rows

I am using Postgres 9.3+

Since the text you displayed isn't valid JSON -- is this multiple rows or is it supposed to be an array of json objects? — Joe Love
– Joe Love, Commented Mar 16, 2015 at 7:11
@JoeLove sorry for incorrect value, now i have corrected it, please view it — mukund
– mukund, Commented Mar 16, 2015 at 16:12

Raj Srivastava · Accepted Answer · 2015-03-18 20:35:39Z

1

Its a nested query and I have tried writing it in as readable form as I can. I hope you can understand it -

select subscriptions from
(
    select
        cast
        (
            json_array_elements
            (
                json_array_elements(data)->'client'->'subscription'
            )
            as text
        )
        as subscriptions,

        json_array_elements
        (
            json_array_elements(data)->'client'->'subscription'
        )
        ->>'interval'
        as intervals

    from
        transactions
)
as 
    xyz
where
     intervals = '1 WEEK';

For information regarding these functions, you can refer to - http://www.postgresql.org/docs/9.3/static/functions-json.html

Edit:-

As per performance requirements, I guess this will work better than the previous one -

select * from (
  select cast (
    json_array_elements (
      json_array_elements(data)->'client'->'subscription'
    ) as text
  ) as subscription from transactions
) as temp
where subscription LIKE '%"interval":"1 WEEK"%';

edited Mar 18, 2015 at 20:35

answered Mar 16, 2015 at 19:15

Raj Srivastava

7959 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mukund Over a year ago

Answer seems to be correct, but do u think its optimal? I tried to execute and seen the performance of above query for only two rows, means with above mentioned data only and showed me 10400000 row initially during operation? So for large data it will be unpredictable

Collectives™ on Stack Overflow

Postgres get search and get multiple array json to each row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related