CODEWITHSUNDEEP
postgresqlpostgresql-9.6
sds
sds

Reputation: 60054

Is it possible to control the JSONB serialization?

I would like to export a jsonb column in a Postgres (v9.6) table.

I tried the COPY command:

\COPY (SELECT my_json_column FROM my_table) to 'data.json';

I did get a JSON file, but I would like two modifications:

  1. I want to conserve space: no spaces after delimiters : and ,.
  2. I want a uniform and predictable key sorting.

In other words, I want the behavior of the Python json.dump with arguments

  1. separators=(',',':')
  2. sort_keys=True

(This is the format of the ingested file, and I want to export it in the same format to check that the roundtrip is identity.)

Upvotes: 2

Views: 1314

Answers (2)

klin
klin

Reputation: 121764

There are no built-in options to format jsonb output. You can use PL/Python.

create extension if not exists plpython3u;

As for now (Postgres 10) you have to use Abstract Syntax Trees to convert Postgres jsonb to a Python object (automatic conversion will be possible in future releases).

create or replace function format_jsonb(obj jsonb)
returns text language plpython3u as $$
    import json
    import ast
    js = ast.literal_eval(obj);
    return json.dumps(js, separators=(',', ':'), sort_keys=True)
$$;

Use: 

select format_jsonb('{"abcd": 1, "jklm": 2, "jkl": 3, "abc": 4}');

            format_jsonb             
-------------------------------------
 {"abc":4,"abcd":1,"jkl":3,"jklm":2}
(1 row)

So your command may look like this:

\COPY (SELECT format_jsonb(my_json_column) FROM my_table) to 'data.json';

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 247270

If you want to preserve your original format, don't use jsonb. Use json which stores the value as you input it. Even with the output massage you envision, duplicate keys would be lost.

If you need jsonb facilities, cast the column to jsonb on the fly. You can still be efficient, because you can add an index like

CREATE INDEX ON mytab USING gin ((jsoncol::jsonb));

Upvotes: 0

Related Questions