Inconsistent behavior of json_to_recordset/jsonb_to_recordset in PostgreSQL >=10 version
Recently we have upgraded from PostgreSQL 9.6 to 11 version. Post upgradation we started observing inconsistent behavior of json/jsonb_to_recordset functions.
JSON embedded inside the JSON as a string can be converted to records using json/jsonb_to_recordset functions.
In PostgreSQL 9.6, it is able to parse the json inside json and parse the escape characters as special symbols.
postgres=# select * from json_to_recordset('[{"a":"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}","b":1},{"a":"2","b": 2}]') as x(a jsonb, b int);
a | b
-----------------------------------------------------+---
{"test": "test", "type_id": "9", "currency": "INR"} | 1
2 | 2
(2 rows)In PostgreSQL 10/11/12, It is parsing the escape symbols as a part of json string giving them in output.
postgres=# select * from json_to_recordset('[{"a":"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}","b":1},{"a":"2","b": 2}]') as x(a jsonb, b int);
a | b
--------------------------------------------------------------+---
"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}" | 1
"2" | 2
(2 rows)To achieve the same behavior as PostgreSQL 9.6 in PostgreSQL 10/11/12, convert the json string to text and then convert to jsonb/json.
postgres=# select x.a::json,b from json_to_recordset('[{"a":"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}","b":1},{"a":"2","b": 2}]') as x(a text, b int);
a | b
------------------------------------------------+---
{"type_id":"9","test":"test","currency":"INR"} | 1
2 | 2
(2 rows)
Viswamitra's blog 