while copy data to in json file in postgresql using query , record with single "\" is replaced with "\\"

Question

I have postgresql copy command like

\copy  (select row_to_json(xyz) from (select employeeJson from employee where empid='1006') xyz) to '/home/users/emp_create_cp_1006.json';

emplyeejson is a jsonb column and having record like

{
"empid": 1006,
"userId":"rirani",
"jobTitleName":"Developer",
"firstName":"Bala",
"lastName":"K",
"preferredFullName":"Bala K",
"employeeCode":"E1",
"region":"CA",
"phoneNumber":"408-1234567",
"emailAddress":"[email protected]",
"address" : "10\" sarah apartment"
}

when the file is generated ,the address value become "10\" sarah apartment" ,which is a invalid json.

If employeejson is a jsonb column, then row_to_json(xyz) doesn't really make sense. You are nesting one JSON value into another. It seems you just want: \copy (select employeeJson from employee where empid=1006) to '/home/users/emp_create_cp_1006.json' — user330315
– user330315, Commented Jan 31, 2019 at 10:26

Daniel Vérité · Accepted Answer · 2019-01-31 19:39:29Z

1

COPY TO does not produce verbatim text, it produces a text format in which backslash is a special character, with backlash in the contents being doubled, and whose main purpose is to be reloaded by a COPY FROM.

For your case, you may call SELECT with the unaligned format to suppress blank padding and tuples_only to suppress column names. It produces the exact value of the column without a layer of encoding or decoration.

In psql:

\pset format unaligned
\pset tuples_only
SELECT employeeJson from employee where empid=1006 \g /home/users/emp_create_cp_1006.json

Or from the shell:

$ psql -At [other options] -c 'SELECT employeeJson from employee where empid=1006' > /home/users/emp_create_cp_1006.json

answered Jan 31, 2019 at 19:39

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

Collectives™ on Stack Overflow

while copy data to in json file in postgresql using query , record with single "\" is replaced with "\\"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related