jsonb_query.py

import psycopg2
import uuid
import json

conn = psycopg2.connect('dbname=ftest user=ffer password=omgnowai')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS entries')
cur.execute('''
  CREATE TABLE entries (
    id serial PRIMARY KEY, 
    sku text, 
    account_id integer, 
    data jsonb
  )
''')
conn.commit()


###
### ADD SOME ENTRIES
###

entries = [
  {
    "AccountIdSkuCompound": "4_MEE-000",
    "Guid": "3a9f184e-2a58-40f2-ba3a-c17c8210a756",
    "Primary": [
      {
        "collectedat": "2014-04-10 18:03:27 -0400",
        "grossweight": 78.8,
        "guid": "2B4250A1-7568-4E0B-8A7D-ACDB1DAB8E55",
        "location": "Bookstore",
        "sampleidentifier": "Baseline",
        "tareweight": 16,
        "tareweightoverride": "NULL",
        "username": "spai@illinois.edu",
        "wastetype": "Organics"
      }
    ],
    "Revision": "0",
    "Sku": "MEE-000",
    "SurveyName": "Import Data 36",
    "Username": None,
    "Version": None
  },
  {
    "AccountIdSkuCompound": "4_MEE-000",
    "Guid": "7d63a9af-a8d8-4a49-abcc-4cdaec5dca0c",
    "Primary": [
      {
        "collectedat": "2014-04-28 14:14:13 -0400",
        "grossweight": 35.2,
        "guid": "B24196DA-4758-4379-9330-7BCC166C7955",
        "location": "ISTC",
        "sampleidentifier": "Baseline",
        "tareweight": 4.22,
        "tareweightoverride": "NULL",
        "username": "spai@illinois.edu",
        "wastetype": "AdditionalCatergory2"
      }
    ],
    "Revision": "0",
    "Sku": "MEE-000",
    "SurveyName": "Import Data 19",
    "Username": None,
    "Version": None
  },
  {
    "AccountIdSkuCompound": "4_MEE-000",
    "Guid": "27b12d6a-6db3-4074-89d5-6349f3041f48",
    "Primary": [
      {
        "collectedat": "2014-04-10 18:03:27 -0400",
        "grossweight": 2,
        "guid": "2B4250A1-7568-4E0B-8A7D-ACDB1DAB8E55",
        "location": "Bookstore",
        "sampleidentifier": "Baseline",
        "tareweight": 2,
        "tareweightoverride": "NULL",
        "username": "spai@illinois.edu",
        "wastetype": "Metals"
      }
    ],
    "Revision": "0",
    "Sku": "MEE-000",
    "SurveyName": "Import Data 49",
    "Username": None,
    "Version": None
  }
]

for i, entry in enumerate(entries):
  cur.execute('''
    INSERT INTO entries(sku, account_id, data)
    VALUES('TEST-001', %s, %s)
  ''', (i, json.dumps(entry)))

conn.commit()

###
### FILTERING ON SCALAR VALUES INSIDE COLLECTIONS OF OBJECTS
###

cur.execute('''
  SELECT * FROM entries, 
  LATERAL jsonb_array_elements_text(entries.data->'Primary') jv(j) 
  WHERE cast(jv.j::JSONB->>'grossweight' as double precision) < 6.0
''')

print cur.fetchone()