Return Multiple Columns of the Same Row as Json Array of Objects

Return multiple columns of the same row as JSON array of objects

json_build_object() in Postgres 9.4 or newer

Or jsonb_build_object() to return jsonb.

SELECT value_two, json_agg(json_build_object('value_three', value_three
                                           , 'value_four' , value_four)) AS value_four
FROM   mytable 
GROUP  BY value_two;

The manual:

Builds a JSON object out of a variadic argument list. By convention,
the argument list consists of alternating keys and values.

For any version (incl. Postgres 9.3)

row_to_json() with a ROW expression would do the trick:

SELECT value_two
     , json_agg(row_to_json((value_three, value_four))) AS value_four
FROM   mytable
GROUP  BY value_two;

But you lose original column names. A cast to a registered row type avoids that. (The row type of a temporary table serves for ad hoc queries, too.)

CREATE TYPE foo AS (value_three text, value_four text);  -- once in the same session

SELECT value_two
     , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
FROM   mytable
GROUP  BY value_two;

Or use a subselect instead of the ROW expression. More verbose, but without type cast:

SELECT value_two
     , json_agg(row_to_json((SELECT t FROM (SELECT value_three, value_four) t))) AS value_four
FROM   mytable
GROUP  BY value_two;

More explanation in Craig's related answer:

• PostgreSQL 9.2 row_to_json() with nested joins

db<>fiddle here

_{Old sqlfiddle}

Select row with all related child rows as array in one query

One of many ways:

SELECT jsonb_pretty(
          to_jsonb(o.*)  -- taking whole row
      || (SELECT jsonb_build_object('items', jsonb_agg(i))
          FROM  (
             SELECT name, url  -- picking columns
             FROM   items i
             WHERE  i.order_id = o.id
             ) i
         )
       )
FROM   orders o
WHERE  o.id = 12345;

This returns formatted text similar to the displayed input. (But keys are sorted, so 'total' comes after 'items'.)

If an order has no items, you get "items": null.

For a jsonb value, strip the jsonb_pretty() wrapper.

I chose jsonb for its additional functionality - like the jsonb || jsonb → jsonb operator and the jsonb_pretty() function.

Related:

• Return multiple columns of the same row as JSON array of objects

If you want a json value instead, you can cast the jsonb directly (without format) or the formatted text (with format). Or build a json value with rudimentary formatting directly (faster):

SELECT row_to_json(sub, true)
FROM  (
   SELECT o.*
       , (SELECT json_agg(i)
          FROM  (
             SELECT name, url  -- pick columns to report
             FROM   items i
             WHERE  i.order_id = o.id
             ) i
         ) AS items
   FROM   orders o
   WHERE  o.id = 12345
   ) sub;

db<>fiddle here

It all depends on what you need exactly.

Aside:

Consider type text (or varchar) instead of the seemingly arbitrary varchar(128). See:

• Should I add an arbitrary length limit to VARCHAR columns?

how to get multiple values from a json array/object using jq

With .[].name + " " + .[].id' you iterate twice over the array. Iterate once and extract your data in one go:

curl … | jq -r '.data[] | .name + " " + .id'

Netbank734113 8a70803f8045722601804f62d54c5d9d
Netbank734112 8a70801c804568ae01804f625a923f8d

Demo

---

You might also be interested in using string interpolation:

curl … | jq -r '.data[] | "\(.name) \(.id)"'

Demo

JSON returning multiple columns, but all values as single string. How do I get matching key-value from array?

For the JSON:

{"COLUMNS":["ABC","DEF","GHI"],"DATA":[[11,27,4]]}"

The property DATA is an array of arrays.

Consider it like this: DATA = [a, b, c], where a, b and c are variables. The thing is that your a is another array, just as DATA is.

This way DATA[0], the first element of the DATA array, is an array.

How do I get DATA[0], which should be 11?

The value you want is in: DATA[0][0]:

Because:

DATA[0] -> [11,27,4]

Then:

DATA[0][0] -> 11

DATA[0][1] -> 27

DATA[0][2] -> 4

Return multiple COLUMN_JSON results as JSON array

Turns out it was GROUP_CONCAT that I needed, and specifying a comma as the delimiter. So changing my SQL to:

select
    e.id, 
    CONVERT(
        GROUP_CONCAT(
            COLUMN_JSON(
                COLUMN_CREATE(
                    'role', em.muscleRoleName, 
                    'muscle', em.muscleName
                )
            )
            SEPARATOR ','
        ) USING utf8) as muscles 
from
    exercise e
    inner join exerciseMuscle em
            on e.id = em.exerciseId
where
    e.id = 96;

Returns:

| id | musclesJson 
| 96 | {"role":"main","muscle":"biceps"},{"role":"secondary","muscle":"shoulders"}

PostgreSQL: Aggregate multiple rows as JSON array based on specific column

Disclaimer: Only for Postgres 9.4+ 9.4 and further version add huge support for JSON to Postgres. 9.3 is already unsupported. You should really upgrade your database.

demo:db<>fiddle

SELECT json_agg(trips)
FROM (
    SELECT 
        json_agg(
            json_build_object(
                'recorded_at', created_at, 
                'latitude', latitude, 
                'longitude', longitude
            )
        ) as trips
    FROM data_tracks
    GROUP by trip_log_id
)s

1. json_build_object creates your main json objects
2. json_agg() ... GROUP BY trip_log_id groups these json objects into one trip object
3. second json_agg aggregates all trips into one array

---

demo:SQL Fiddle

Version for 9.3 (strictly not recommended!)

SELECT json_agg(trips)
FROM (
    SELECT 
        json_agg(
            ('{"recorded_at":"' || created_at || 
            '","latitude":' || latitude || 
            ',"longitude":' || longitude || '}')::json
        ) as trips
    FROM data_tracks
    GROUP by trip_log_id
)s

How can I filter() a value between several columns of my JSON? Javascript

Works without needing the pass the column, searches all the available columns.

One line code

This filter goes thru each key that you have to check values and return the result.

Detailed explanation:

1) Base is the object you posted and assuming you want to filter the "data"
2) First filter goes thru all the elements in the array
3) second filter goes thru all the keys in the object which is element in the array
4) Checking if the column has the data 
5) returns the array of elements if matched

const text = 'Test 2';
return array.filter(v=> Object.keys(v).filter(key=> v[key].toString().toLowerCase().includes(text.toString().toLowerCase())).length > 0)

And result is:

---

Related Topics