Cannot dump JSON object with Null values to string

[mgburns]

Use Case

I'm using the CLI to import JSON Line log files into a Postgres database. One of the columns I'm trying to import is jsonb, and needs to be inserted as a JSON string.

Approach

spyql 'IMPORT json SELECT json.dumps(.a) FROM {"a": {"foo": 1, "bar": 2}} TO sql'
INSERT INTO "table_name"("dumps_a") VALUES ('{"foo": 1, "bar": 2}');

This works fine until it hits a null value:

spyql 'IMPORT json SELECT json.dumps(.a) FROM {"a": {"foo": 1, "bar": null}} TO sql'
ERROR   could not evaluate SELECT expression #1: json.dumps(row.a)
    at data row #1: [{'a': {'foo': 1, 'bar': NULL}}]
TypeError: Object of type NullType is not JSON serializable

I also tried passing the default kwarg to json.dumps and hit a different error:

spyql 'IMPORT json SELECT json.dumps(.a, default=str) FROM {"a": {"foo": 1, "bar": null}} TO sql'
ERROR   could not evaluate SELECT expression #1: json.dumps(row.a, default=str)
    at data row #1: [{'a': {'foo': 1, 'bar': NULL}}]
ValueError: Circular reference detected

Very open to alternative approaches; thank you so much for making this great tool available. :)

[dcmoura]

Thank you @mgburns for your feedback and for reaching out!

This use case is not supported... but makes sense supporting it (e.g. by adding a dump_json helper function). Right now, you can do the following workaround:

$ spyql 'IMPORT json AS js SELECT js.dumps(.a, default=lambda x: None if x is null else str(x)) AS a FROM {"a": {"foo": 1, "bar": null}} TO sql(table="mytable")'

INSERT INTO "mytable"("a") VALUES ('{"foo": 1, "bar": null}');

Please let me know if it solves your issue. Thanks!

[mgburns]

Gah, great solution -- that did the trick! Thanks for the quick workaround.