Question 247086 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Remove jsonb_build_object from the output

I am creating a jsonb object using jsonb_build_object in PostgreSQL. The problem is that it adds jsonb_build_object column in the output. Please refer to the screenshot and query below:

enter image description here

     select  jsonb_build_object(
                    'country_name', country_name,
                    'country_code', country_code,
                    'currency_code', currency_code
                )
     from table

Is there any way to exclude jsonb_build_object from output?

Tags:

3

Answers


  1. 0

    Try using CTE (Common Table Expression) to achieve the desired output.

    Here is the CTE Code;

    WITH cte AS (
    SELECT jsonb_build_object(
        'country_name', country_name,
        'country_code', country_code,
        'currency_code', currency_code
    ) AS jsonb_data
    FROM table
)
SELECT jsonb_data
FROM cte;

    Now the ‘jsonb_build_object’ has been excluded from the output.

    Login or Signup to reply.
  2. 0

    try this:

    with temp_data as (
  select  jsonb_build_object(
                    'country_name', country_name,
                    'country_code', country_code,
                    'currency_code', currency_code
                )
     from table
)

select t->'jsonb_build_object' from temp_data t
    Login or Signup to reply.
  3. 0

    As pointed out by @jjanes, that’s not PostgreSQL doing it, it’s your client application or library fetching results of your query into a json structure where it injects the column/field name as a top-level key in order to be able to accommodate multiple columns in one structure.

    You didn’t specify how you wish to call the field holding the ouput of that function, so PostgreSQL just named it after the function, hence the jsonb_build_object top-level key, but according to the db it’s the field/column name, not an internal part of the result. You can see what it looks like to the DB here: demo

    create table your_table as select 
  'Canada' as country_name, 
  'CA' as country_code, 
  'CAD' as currency_code;

select  jsonb_build_object(
                    'country_name', country_name,
                    'country_code', country_code,
                    'currency_code', currency_code
                )
from your_table;
    jsonb_build_object
    {"country_code": "CA", "country_name": "Canada", "currency_code": "CAD"}

    Notice that it’s in the header, not in the actual field. To get rid of it, you need to peel that off in your client/app. It would be helpful if you updated the question to specify what client/library+language you’re using and show how you obtained these records.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search