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.

klin · Accepted Answer · 2018-10-10 21:07:49Z

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, 2018 at 21:07

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

BTL-Uni Over a year ago

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.

klin Over a year ago

cross join <function> 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?

Collectives™ on Stack Overflow

PostgreSQL JSON - Subset of nested lists

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related