Postgresql – How to quote arrays in postgres copy statement to inser into JSON field

Let's create the table:

mysql> create table jsontest (id serial, value json);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table jsontest\G
*************************** 1. row ***************************
Table: jsontest
Create Table: CREATE TABLE `jsontest` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` json DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And insert some JSON data/objects:

mysql> INSERT INTO jsontest(value) VALUES (JSON_OBJECT('test', 'string with "escaped quotes" does not work'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO jsontest(value) VALUES ('{"test": "string with \\"escaped quotes\\" does not work"}');
Query OK, 1 row affected (0.01 sec)

mysql> select id, value->>"$.test" from jsontest;
+----+--------------------------------------------+
| id | value->>"$.test"                           |
+----+--------------------------------------------+
|  2 | string with "escaped quotes" does not work |
|  9 | string with "escaped quotes" does not work |
+----+--------------------------------------------+
2 rows in set (0.00 sec)

As you can see here you have to use the double backslash escape sequence because you don't want the SQL parser to perform the standard escape sequence processing but instead you want to pass the literal string containing the escape sequence down to the storage engine for the JSON data type processing.

I'll talk to the MySQL docs team about adding some examples and explanation for that here:

12.6 The JSON Data Type - Creating JSON Values

If you want to use dynamic commands, you need the right context for it. Plain SQL is not one.

One possibility is using PL/pgSQL, and it's EXECUTE functionality. Use DO to get into the context:

DO $$
DECLARE page integer;
BEGIN
FOR page IN SELECT i FROM generate_series(1, 10) AS t(i)
LOOP
    EXECUTE format($cmd$ COPY _temp 
                         FROM PROGRAM 'curl -uri "https://url?&page=%s"'$cmd$,
                   page::text);
END LOOP;
END; $$;

The other way is to run the loop on the shell side. The following example is using bash:

for i in $(seq 1 10); do
    psql -c "COPY _temp FROM PROGRAM 'curl -uri https://url?&page=${i}'"
done

Notes:

• I am using two levels of dollar-quoting ($$ and $cmd$) to prevent quote multiplication.
• I am also using format() instead of the messy concatenation.
• If you really have to quote the URI of the curl command, you will have to escape the quotes in there.