How to format my JSON input to SQL insert statements

Question

How can I (generically) transform the input file below to output file below, using jq:

Input file:

[{"a": 1, "b": 10},
 {"a": 2, "d": "fred", "e": 30}]

Output file:

INSERT INTO mytab (a,b) VALUES (1,10);
INSERT INTO mytab (a,d,e) VALUES (2,"fred",30);

Inian · Accepted Answer · 2022-03-16 06:45:59Z

2

Using a combination of string interpolation and two variants of string join operations, one using the @csv filter and other using join(",")

jq --raw-output '
  .[]| to_entries | map(.key) as $k | map(.value) as $v | 
    "INSERT INTO mytab (\($k | join(","))) VALUES (\($v | @csv ));"'

answered Mar 16, 2022 at 6:38

Inian

87k15 gold badges166 silver badges183 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Re "map(to_entries)[]", .[] | to_entries. Seems really weird to build an array only to flatten it immediately afterwards.

If its just a matter of style, I'd like to keep it this way.

Also, @csv? Will that do the right thing for strings that contain " or \ ?

@ikegami I'll leave it to the OP to decide, they want the values field to retain the original format, so can't use join for it

I don't understand. Whether @csv produces SQL literals or not is not something that needs to be decided.

|