SELECT / Querying "into" JSON arrays

Question

I have a PostgreSQL 9.4.5 database. There's a table itemtable with a JSONB column called data, which contains sets of information like so (lots of stuff stripped for brevity):

{ "items": [
    { "id": 158597 },
    { "id": 156300 },
    (...)
] }

Now, I want to get a list of all the ids in those sets of items - informally one might say SELECT id FROM items FROM data FROM itemtable. I've only managed to do that using a subquery, which is obviously suboptimal:

SELECT idlist->>'id' FROM ( 
   SELECT json_array_elements(data::json->'items') AS idlist FROM itemtable 
) AS necessary_subquery_name

How can I do that less convoluted?

Please note that I'd need to run this in SquirrelSQL, so the ::json cast is needed.

score 2 · Accepted Answer · answered Oct 27 '15 at 11:11

2

Oh well, nevermind, I must have missed a coffee today. It's obviously:

SELECT json_array_elements(data::json->'items')->>'id' FROM itemtable

answered Oct 27 '15 at 11:11

zb226

9,586
6
49
79

SELECT / Querying "into" JSON arrays

1 Answers1