title: SQLAlchemy, PostgreSQL and array_agg: How to select items from array_agg?
body: <p>I want to use <a href="http://www.postgresql.org/docs/8.4/static/functions-aggregate.html" rel="noreferrer">array_agg</a> in a subquery, then make use of the aggregated data by it's array index in my main query, however, after trying many different ways, I'm really at a loss as to how it should be done; could someone explain why in the example below I get a series of None values instead of the first category in the array?</p>
  
  <p>I understand that the following, simplified example can be done without doing a SELECT on array[i], but it will explain the nature of the problem:</p>
  
  <pre><code>from sqlalchemy import Integer
  from sqlalchemy.dialects.postgres import ARRAY
  
  prods = (
      session.query(
          Product.id.label('id'),
          func.array_agg(ProductCategory.id, type_=ARRAY(Integer)).label('cats'))
      .outerjoin(
          ProductCategory,
          ProductCategory.product_id == Product.id)
      .group_by(Product.id).subquery()
  )
  
  # Confirm that there's categories:
  [x for x in session.query(prods) if len(x[1]) &gt; 1][:10]
  """
  Out[48]: 
  [(2428, [1633667, 1633665, 1633666]),
   (2462, [1162046, 1162043, 2543783, 1162045]),
   (2573, [1633697, 1633696]),
   (2598, [2546824, 922288, 922289]),
   (2645, [2544843, 338411]),
   (2660, [1633713, 1633714, 1633712, 1633711]),
   (2686, [2547480, 466995, 466996]),
   (2748, [2546706, 2879]),
   (2785, [467074, 467073, 2545804]),
   (2806, [2545326, 686295, 686298, 686297])]
  """
  
  # Ok now try to query to get the first category of each array:
  [x for x in session.query(prods.c.cats[0].label('first_cat'))]
  
  """
   (None),
   (None),
   (None),
   (None),
   (None),
   (None),
   (None),
   (None),
   (None),
   (None),
   (None),
  """
  </code></pre>