json_to_record with embedded encoded json in PostgreSQL >= 10

Question

With PostgreSQL 9.6, it's possible to convert JSON embedded as a string within JSON as follows:

# select * from json_to_record('{"key":"{\"embedded\": 42}"}') as x(key json);
       key        
------------------
 {"embedded": 42}
(1 row)

This fails with PostgreSQL 10 and above:

# select * from json_to_record('{"key":"{\"embedded\": 42}"}') as x(key json);
ERROR:  invalid input syntax for type json
DETAIL:  Token "embedded" is invalid.
CONTEXT:  JSON data, line 1: "{"embedded...

The PostgreSQL 10 release notes mention changes to populate_json and related functions (including, presumable json_to_record), apparently to deal with non-quoted embedded JSON:

With this change, array-type fields in the destination SQL type are properly converted from JSON arrays, and composite-type fields are properly converted from JSON objects. Previously, such cases would fail because the text representation of the JSON value would be fed to array_in() or record_in(), and its syntax would not match what those input functions expect.

It seems the error when used with quoted JSON is an accidental side-effect of this change. Is there a way achieve the 9.6 behavior with the newer versions?

sticky bit · Accepted Answer · 2019-05-27 15:42:27Z

2

You can first get the column as text and then cast it to json.

SELECT x.key::json
       FROM json_to_record('{"key":"{\"embedded\": 42}"}') x(key text);

db<>fiddle

answered May 27, 2019 at 15:42

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

json_to_record with embedded encoded json in PostgreSQL >= 10

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