could not determine polymorphic type because input has type unknown

Question

I am trying to edit only a few attributes in a db jsonb element. This is how my table_ioc value will look in DB.

[{"id": 6, "tlp": "AMB",    "type": "TTP", "value": "TYYY", "nature": "danger", "threat_actor": "Row"  },
 {"id": 7, "tlp": "YELLOW", "type": "TTP", "value": "T888", "nature": "light",  "threat_actor": "White"}]

I am using the code below:

if "index" in locals():
    try:
        query = f"UPDATE ti_report_staging SET table_ioc = jsonb_set(table_ioc, '{{{index}}}', jsonb_set(jsonb_set(jsonb_set(jsonb_set(jsonb_set(table_ioc -> :index, '{{type}}', to_jsonb(:type)), '{{nature}}', to_jsonb(:nature)), '{{tlp}}', to_jsonb(:tlp)), '{{threat_actor}}', to_jsonb(:threat_actor))), true) WHERE pk = :pk;"

        await session.execute(text(query), {
            "index": index,
            "type": data.get("type"),
            "nature": data.get("nature"),
            "tlp": data.get("tlp"),
            "threat_actor": data.get("threat_actor"),
            "pk": pk,
        })
        print("Updated IOC successfully.")
    except Exception as e:
        print(f"Database Error: {e} .", e)
        await session.rollback()
        print("Rollback completed.")

This gives the error:

Database Error: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DatatypeMismatchError'>: could not determine polymorphic type because input has type unknown
[SQL: UPDATE ti_report_staging SET table_ioc = jsonb_set(table_ioc, '{2}', jsonb_set(jsonb_set(jsonb_set(jsonb_set(jsonb_set(table_ioc -> %s, '{type}', to_jsonb(%s)), '{nature}', to_jsonb(%s)), '{tlp}', to_jsonb(%s)), '{threat_actor}', to_jsonb(%s))), true) WHERE pk = %s;]
[parameters: (2, 'TTP', 'malicious', 'RED', 'White', '2OtBpaVpy2kPej9yGGfIB7YjqRT')]
(Background on this error at: https://sqlalche.me/e/14/f405) . (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DatatypeMismatchError'>: could not determine polymorphic type because input has type unknown
[SQL: UPDATE ti_report_staging SET table_ioc = jsonb_set(table_ioc, '{2}', jsonb_set(jsonb_set(jsonb_set(jsonb_set(jsonb_set(table_ioc -> %s, '{type}', to_jsonb(%s)), '{nature}', to_jsonb(%s)), '{tlp}', to_jsonb(%s)), '{threat_actor}', to_jsonb(%s))), true) WHERE pk = %s;]
(Background on this error at: https://sqlalche.me/e/14/f405). 

could you please help me fix this?

I need my query to be inserted. It should update tlp, threat_actor, and nature for the given id.

Answer 1

Try explicitly casting the values to the desired type(s) before using them in the jsonb_set function by including the CAST function. e.g.

query = f"""UPDATE ti_report_staging
            SET table_ioc = jsonb_set(table_ioc, '{{{index}}}',
                    jsonb_set(jsonb_set(jsonb_set(jsonb_set(jsonb_set(
                        CAST(table_ioc -> :index AS jsonb), '{{type}}', to_jsonb(:type)),
                        '{{nature}}', to_jsonb(:nature)), '{{tlp}}', to_jsonb(:tlp)),
                        '{{threat_actor}}', to_jsonb(:threat_actor))), true)
            WHERE pk = :pk;"""