Sequelize Postgres query JSONB ARRAY

Question

Have a table Profile with a column named records which is ARRAY of JSONB. The records column is in the following shape:

[
  {
    itemId: 1,
    value: true,
    insertOn: '2018-03-26T03:49:55.121Z',
  },
  {
    itemId: 1,
    value: false,
    insertOn: '2018-03-27T03:49:55.121Z',
  },
];

What I am looking forwards to find is the latest value of a given itemId. For example, the latest value of itemId 1 is false.

How can I implement the query? I've tried sequelize.fn('UNNEST', sequelize.col('records')) but dont know how to continue.

Andomar · Accepted Answer · 2018-03-26 06:05:35Z

1

You can use jsonb_array_elements to expand a JSON column to a rowset. Then you can use ->> to access its elements:

select  ele->>'value'
from    YourTable
cross join lateral
       jsonb_array_elements(col1) arr(ele)
where  ele->>'itemId' = '1'
order by
       ele->>'insertOn' desc
limit  1

Working example at SQL Fiddle.

answered Mar 26, 2018 at 6:05

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

2 Comments

Steven Luo Over a year ago

Thanks for your replay. But how can I implement it in Sequelize 4?

Andomar Over a year ago

I can do it in SQL, but don't know much about Sequelize:)

Collectives™ on Stack Overflow

Sequelize Postgres query JSONB ARRAY

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related