1

I am looking for a simple/clean way that can be used consistently to automatically input JavaScript nested objects to user-defined PostgreSQL functions and also automatically output to JavaScript nested objects.

Shall I used nested composite types? JSON? other ways? How to parse to/from these types and JavaScript nested objects automatically? Any better thoughts?

Thank you.

1
  • 1
    A "JavaScript object" is JSON (=JavaScript Object Notation) Jul 2, 2017 at 13:27

1 Answer 1

1

JavaScript nested objects can be easily (and I'd say "naturally") represented using JSON. This is "by definition" of what JSON (JavaScript Object Notation) is.

So, in your use-case use JSON

On the JavaScript side:


In PostgreSQL, on top of using json data type, you might also use jsonb.

It's got a number of advantages (smaller size, indexability, more functions), and a few differences with json (order of fields is not preserved, you can't have {"a":1,"a":2}, and a few other smallish differences that normally don't matter). See Explanation of JSONB introduced by PostgreSQL. Look also at the second answer.


JSON is not only available in PostgreSQL. Most current databases (MySQL, MS SQL Server, Oracle, SQLite with the JSON1 extension, among others) also support it.

1
  • 1
    Inputs from JavaScript are best passed to PLPGSQL functions via a single jsonb argument, within the functions query results can be stored in jsonb variables using jsonb_agg and jsonb_build_object functions, outputs can be passed as a single jsonb also using jsonb_build_object where libraries such as Node pg library would interpret as native JavaScript object. This is fantastic thank you.
    – geeko
    Jul 4, 2017 at 10:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.