Using Json in Postgres and Psycopg

Importantly we just use an adaptor when inserting

      cur.execute("""INSERT INTO boing 
        ( stem, name, details ) 
        VALUES ( %s, %s, %s )""",

And then query via

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):

     cur.execute("""
          SELECT stem FROM snarf
          WHERE ( ( %s = ANY( things ) )
          AND (details->'Wibble' = %s) ) """,(name,
                              psycopg.types.json.Jsonb(needle)))