title: Using Json in Postgres and Psycopg
tags: python db psql
h1:

Importantly we just use an adaptor when inserting
```py
      cur.execute("""INSERT INTO boing 
        ( stem, name, details ) 
        VALUES ( %s, %s, %s )""",
```

And then query via
```sql
SELECT data->'images'->'thumbnail'->'url' AS thumb FROM instagram;
```

When matching parts of json, we may bump into
```
psycopg.errors.InvalidTextRepresentation: invalid input syntax for type json
```
The solution here is to convert a string into json or jsonb first with e.g. `psycopg.types.json.Jsonb(needle)` (depending on column type):
```py
     cur.execute("""
          SELECT stem FROM snarf
          WHERE ( ( %s = ANY( things ) )
          AND (details->'Wibble' = %s) ) """,(name,
                              psycopg.types.json.Jsonb(needle)))
```