How to store json value using JDBC appender

Question

I would like to store a JSON field through JDBC appender using Log4j 2. This is my configuration and I am not able to store into PostgreSQL JSON type column:

<Configuration status="WARN">
    <Appenders>
        <JDBC name="databaseAppender" tableName="logs.logs">
            <ConnectionFactory
                class="eu.dedalus.phaedra.log.appender.ConnectionFactory"
                method="getDatabaseConnection" />
            <Column name="id" literal="nextval('logs.logs_id_seq')" />
            <Column name="date" isEventTimestamp="true" />
            <Column name="level" pattern="%level" isUnicode="false" />
            ...
            <Column name="data" ??? />
        </JDBC>
    </Appenders>
    <Loggers>
        <Root level="info">
            <AppenderRef ref="databaseAppender" />
        </Root>
    </Loggers>
</Configuration>

This is my meta table definition in PostgreSQL:

CREATE TABLE logs (
    id int4 NOT NULL,
    message varchar NULL,
    fullinfo varchar NULL,
    "level" varchar NULL,
    ...
    "data" json NULL,
    CONSTRAINT id_logs PRIMARY KEY (id)
);

My Java code fills a MapMessage and then calls info method of ExtendedLogger:

final LoggerContext ctx = (LoggerContext) LogManager.getContext(false);
ExtendedLogger log = ctx.getLogger(clazz.getName());
MapMessage map = new MapMessage();
... // put all other data into MapMessage
map.put("data", new JSONObject()) // or JsonNode, String or whatever else
log.info(map);

How to store "data" field into a PostgreSQL JSON column using ConnectionFactory of JDBC Appender? Is it possible using Log4j 2?

score 2 · Accepted Answer · edited Jan 29 '22 at 17:21

You could use the "columnMapping" tag instead of "column" which supports the "parameter" attribute where you can insert an expression using '?' as a marker and adding a cast to json. In your case:

<ColumnMapping name="data" pattern="%K{data}" parameter="?::json" />

or if data can be null, it can check it using COALESCE. It has to be combined to NULLIF because by default K of undefined key returns a empty string

<ColumnMapping name="data" pattern="%K{data}" parameter="COALESCE(NULLIF(?,''),'[]')::json" />

How to store json value using JDBC appender

1 Answers1