PostgreSQL - JSON function-CodePudding

There's a different format for row_to_json and json_build_object.

For row_to_json the input is:

select ...  <table.field>   "<json_key"

And for the json_build_object:

 "<json_key" , <table.field>

That's surprising to me. Oracle uses similar JSON functions with similar input. This may look like a minor thing, but it makes it harder for me to automate the generation of SELECT statements for complex/nested structures.

Consider this simple example:

CREATE TABLE "users" (
    id SERIAL NOT NULL,
    name VARCHAR(100),
    email_address VARCHAR(150),
    PRIMARY KEY(id)
  );

 INSERT INTO "users" ("id", "name", "email_address")
  VALUES (1, 'user1', '[email protected]'), (2, 'user2', '[email protected]');

To fetch the email and the name where email is a dictionary object:

"{""user_name"":""user1"",""email"":{""email_address"" : ""[email protected]""}}"

The query would be:

select row_to_json(users) from 
(select users.name "user_name" ,
 (json_build_object(  'email_address' , users.email_address )) email
  from users ) users;

I would like to use the same order of fields in both cases. The input would be:

<table.field> "<json_key" OR  "<json_key" <table.field>

I tried to flip the order using "AS" but it doesn't work:

select row_to_json(users) from 
(select users.name "user_name" ,
 (json_build_object(   users.email_address  as 'email_address' )) email
  from users ) users;

Maybe with using only row_to_json() or json_build_object()? Or anything else? (But no left join.)

CodePudding user response：

row_to_json takes an entire row tuple (a record) as input, such as you get it from a subquery. If you don't use those, or don't like row_to_json, don't use it. It's a convenience mainly if you use SELECT * and not want to specify column/field names.

I recommend to use json_build_object with explicit field names, especially if you want to rename them anyway. Your query can and should be written as

SELECT json_build_object(
  'user_name', users.name,
  'email', json_build_object(
    'email_address', users.email_address
  )
) AS json_result
FROM users;