PostgreSQL JSON - Subset of nested lists

Question

I have a column in a PostgreSQL database that is basically a jsonified list of python tuples:

[
  ["Mobile","111-111-1111"],
  ["Office","222-222-2222"],
  ["Mobile","333-333-3333"],
  ["Fax","444-444-4444"],
]

I'd like to construct a query that returns a subset of the list based on the first value in each of the nested lists. Below is a psudo-query to hopefully illustrate what I'm after:

SELECT
  foo AS bar,
  (SELECT 
     element 
   FROM 
     phone_numbers 
   WHERE
     element::json->>0 = "Mobile") AS mobile_numbers
FROM
  db
;

mobile_numbers == [["Mobile","111-111-1111"],["Mobile","333-333-3333"]]

I only know bits and pieces about json operators in PostgreSQL (and SQL queries in general), mostly in terms of dictionaries. I can find many examples on here about how to dig into nested dictionaries and return one value, but I haven't found anything that quite matches what I'm after.

Thanks for your help.

score 1 · Accepted Answer · answered Oct 10 '18 at 21:07

1

Assuming the column contains valid json as array of arrays you should unnest the outer array with jsonb_array_elements(), filter inner arrays (tuples) by the first (index 0) their elements and aggregate results with jsonb_agg().

with my_table(phone_numbers) as (
values
('[
  ["Mobile","111-111-1111"],
  ["Office","222-222-2222"],
  ["Mobile","333-333-3333"],
  ["Fax","444-444-4444"]
]'::jsonb)
)

select jsonb_agg(phone)
from my_table
cross join jsonb_array_elements(phone_numbers) as arr(phone)
where phone->>0 = 'Mobile'

                        jsonb_agg                         
----------------------------------------------------------
 [["Mobile", "111-111-1111"], ["Mobile", "333-333-3333"]]
(1 row)

answered Oct 10 '18 at 21:07

klin

112,967
15
204
232

Works great with a little tweak! I used the SELECT statement you wrote as a subquery in my SELECT statement, but instead of a CROSS JOIN, I used a regular JOIN, and the ON statement was ext_table._id = subq_table._id. A CROSS JOIN seemed to yield all "Mobile" numbers in the table for every row in my outer query, whereas the regular join yields the "Mobile" numbers for only that row. Maybe not the most elegant way, but the result is the desired one. – BTL-Uni Oct 15 '18 at 16:59
`cross join ` is always **lateral**, you should not be afraid of a cartesian product, see e.g. [PostgreSQL json_array_elements in FROM clause - why is this not cartesian join?](https://stackoverflow.com/a/33510935/1995738) – klin Oct 15 '18 at 18:28

PostgreSQL JSON - Subset of nested lists

1 Answers1