jsonb-search to only show the spec value

Question

I found most of you questing in this tread but I have problem to get the right bit out of my query,

The jsonb-column looks like this:

[
{"price": 67587, "timestamp": "2016-02-11T06:51:30.696427Z"}, 
{"price": 33964, "timestamp": "2016-02-14T06:49:25.381834Z"}, 
{"price": 58385, "timestamp": "2016-02-19T06:57:05.819455Z"}, etc..
]

the query looks like this:

SELECT * FROM store_product_history 
WHERE EXISTS (SELECT 1 FROM jsonb_array_elements(store_prices) 
as j(data) WHERE (data#>> '{price}') LIKE '%236%');

Which of course gives me the whole rows for the result but I would like to only get like only the timestamps-values from the the rows, is this possible?

Answer 1

If you use jsonb_array_elements() in a lateral join you will be able to select single json attributes, e.g.

with store_product_history(store_prices) as (
values
('[
    {"price": 67587, "timestamp": "2016-02-11T06:51:30.696427Z"}, 
    {"price": 33964, "timestamp": "2016-02-14T06:49:25.381834Z"}, 
    {"price": 58385, "timestamp": "2016-02-19T06:57:05.819455Z"}
]'::jsonb)
)

select data
from store_product_history,
jsonb_array_elements(store_prices) as j(data) 
where (data#>> '{price}') like '%6%';

                             data                             
--------------------------------------------------------------
 {"price": 67587, "timestamp": "2016-02-11T06:51:30.696427Z"}
 {"price": 33964, "timestamp": "2016-02-14T06:49:25.381834Z"}
(2 rows)

Or:

select data->>'timestamp' as timestamp
from store_product_history,
jsonb_array_elements(store_prices) as j(data) 
where (data#>> '{price}') like '%6%';

          timestamp          
-----------------------------
 2016-02-11T06:51:30.696427Z
 2016-02-14T06:49:25.381834Z
(2 rows)