Use jsonb and the function jsonb_set():

create table my_table(id int primary key, jdata jsonb);
insert into my_table values
(1, '{ "object": { "urls": "A;B;C" } }');

update my_table
set jdata = jsonb_set(
        jdata, 
        array['object', 'urls'], 
        to_jsonb(string_to_array(replace((jdata->'object'->'urls')::text, '"',''), ';'))
        )
returning *;

 id |                jdata                
----+-------------------------------------
  1 | {"object": {"urls": ["A", "B", "C"]}}
(1 row) 

Assuming you're using a version of Postgres with JSON support (9.4+), this should get you close:

WITH cte(myJSON) AS (
  SELECT CAST('{"foo":{"bar" :["b","c","d"]}}'AS JSONB) AS MyJSON 

  UNION ALL

  SELECT CAST('{"foo":{"bar" :["e","f","g"]}}'AS JSONB) AS MyJSON  
) 

SELECT
  JSON_AGG(
    (SELECT ROW_TO_JSON(_) FROM (SELECT name) AS _)
  ) myrow
FROM (
  SELECT JSONB_ARRAY_ELEMENTS(myjson->'foo'->'bar') AS name,
    ROW_NUMBER() OVER() AS RowNum
  FROM cte
) src
GROUP BY src.RowNum

This will return [{"name":"b"},{"name":"c"},{"name":"d"}].

You can then construct your final JSON as needed.

SQL Fiddle
Reference

Update
This is very hacky and I'm sure there's a cleaner way, but I updated the query above to handle multiple rows. Just replace the CTE references with your actual table name and myjson with the name of your JSON column.

Output:

[{"name":"b"},{"name":"c"},{"name":"d"}]
[{"name":"e"},{"name":"f"},{"name":"g"}]

Let me know if that works.

Updated Update
Here's an UPDATE statement you can use:

UPDATE t1 tgt
SET jsoncol = JSONB_SET(
  jsoncol, -- Source JSON
  '{foo,bar}', -- Target node to update
  src.new_json -- New value 
)
FROM (
  SELECT 
    ID, 
    JSONB_AGG(
      (SELECT TO_JSONB(_) FROM (SELECT name) AS _) -- Convert new row to JSON
    ) new_json
  FROM (
    SELECT 
      ID, 
      JSONB_ARRAY_ELEMENTS(jsoncol->'foo'->'bar') AS name -- Convert array to rows
    FROM t1
  ) src
  GROUP BY src.ID
) src
WHERE tgt.ID = src.ID -- Update "tgt" table with rows from "src" table
;

DB-Fiddle

In 9.4.4 using the #>> operator works for me:

select to_json('test'::text) #>> '{}';

To use with a table column:

select jsoncol #>> '{}' from mytable;

It is a bit counterintuitive.
This worked for me in postgres 13:

Get an array:

ARRAY(SELECT jsonb_array_elements_text( 
        jsonb_path_query_array(table.jsonb_column, '$.item.*.subitem')
))

Get an array into a string:

array_to_string(ARRAY(SELECT jsonb_array_elements_text( 
        jsonb_path_query_array(table.jsonb_column, '$.item.*.subitem')
)), ' ')

Use jsonb_build_object() and jsonb_agg():

select c.id, jsonb_agg(jsonb_build_object('id', cp.id, 'position', cp.position))
from contacts c
join contactposition cp on c.id = cp.contact_id
group by 1;

Postgres 10 or newer

Postgres 10 improved the functionality of jsonb_to_record() and friends. Now even multi-dimensional JSON arrays can be converted efficiently. Quoting the release notes:

• Make json_populate_record() and related functions process JSON arrays and objects recursively (Nikita Glukhov)

  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.

jsonb_to_record() requires a column definition list, where the output column name(s) must match the key(s), so move it to the FROM clause. Additional benefit: you can coerce to an element type of your choice right away.

SELECT tbl_id, t.data -> 'tags' AS jsonb_arr, j.tags AS txt_arr
FROM   tbl t
LEFT   JOIN LATERAL jsonb_to_record(t.data) AS j(tags text[]) ON true;

fiddle

Or json_to_record() for json input.

The LEFT JOIN is optional to preserve all input rows.

If you still need one, here is function with a "standard SQL" syntax (Postgres 14+):

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
BEGIN ATOMIC
SELECT tags FROM jsonb_to_record($1) AS t(tags text[]);
END;

Scales particularly well for long arrays. The key name 'tags' is hard-coded. It's possible to make the key name dynamic, but costly. Simpler with this variant:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb, _key text)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN ARRAY(SELECT jsonb_array_elements_text(_js -> _key));

Details below.

For very short arrays, string manipulation can be faster:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb, _key text)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN translate(_js ->> _key, '[]', '{}')::int[];

Postgres 9.6 or older

For multi-dimensional arrays see:

• unable to extract json column data (posgres 9.6)

Postgres 9.4 or newer

Inspired by this post, Postgres 9.4 added the missing functions to unnest JSON arrays.
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!

• json_array_elements_text(json)
• jsonb_array_elements_text(jsonb)

Use array_agg() or an ARRAY constructor to build a Postgres array (type text[]) from the resulting set of text.
Or string_agg() to build a string with a list of values (type text).

Focusing on array output (text[]), not string (text). Important difference: null elements are preserved in actual arrays. This is not possible in a string, which cannot contain null values. The true representation is an array.

Replace 'jsonb' with 'json' for type json in all following SQL code.

TLDR: Use a custom function

Encapsulate the logic in a function for repeated use:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

In Postgres 14 or later, consider the new SQL standard form:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN ARRAY(SELECT jsonb_array_elements_text(_js));

See:

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

Call (same for either function):

SELECT tbl_id, jsonb_array_to_text_array(data->'tags')
FROM   tbl;

• LANGUAGE sql for the simple function. (Fastest in my latest tests with Postgres 14.)

• IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow its use in index expressions.

• STRICT to return null for null input. Also: faster. The function cannot be inlined anyway because of the ARRAY constructor / the aggregate function, so STRICT cannot harm that.

• PARALLEL SAFE (in Postgres 9.6 or later!) to allow parallel execution in big queries. See:

  • When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

This function with a STRICT modifier is also as true to the original as possible in that it returns null for null input and an empty array for empty array input. Better than all of the below queries.

For completeness: use to_jsonb() for the reverse SQL array → jsonb conversion.

Various solutions, step-by-step

Immediately aggregate per row in a LATERAL or correlated subquery, then original order is preserved and we don't need ORDER BY, GROUP BY or even a unique key in the outer query. See:

• How to combine ORDER BY and LIMIT with an aggregate function?
• What is the difference between LATERAL and a subquery in PostgreSQL?
• How to combine ORDER BY and LIMIT with an aggregate function?
• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Basic query, returns null for empty array or null input:

SELECT t.tbl_id, d.txt_arr
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT array_agg(d.elem) AS txt_arr
   FROM   jsonb_array_elements_text(t.data->'tags') AS d(elem)
   ) AS d;

Short syntax, returns null for empty array or null input:

SELECT t.tbl_id, d.txt_arr
FROM   tbl t, LATERAL (
   SELECT array_agg(value) AS txt_arr
   FROM   jsonb_array_elements_text(t.data->'tags')  -- default name is "value"
   ) d;

Shorter (and faster) with ARRAY constructor, returns empty array for empty array or null input:

SELECT t.tbl_id, t.data->'tags' AS jsonb_arr, d.txt_arr
FROM   tbl t, LATERAL (
   SELECT ARRAY(SELECT jsonb_array_elements_text(t.data->'tags'))
   ) d(txt_arr);

Even shorter (and faster) with correlated subquery, returns empty array for empty array or null input:

SELECT tbl_id, ARRAY(SELECT jsonb_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

db<>fiddle here

All of the above preserve original order of elements.

Postgres 9.3 or older

Use the function json_array_elements(). But we get double quoted strings from it.

Alternative query with aggregation in the outer query. CROSS JOIN removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY constructor, still with quoted strings:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Note that null is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.

Poor man's unquoting with trim():

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

Retrieve a single row from tbl:

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

Strings form correlated subquery:

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

ARRAY constructor:

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

db<>fiddle here
Old sqlfiddle

Related:

• Need to select a JSON array element dynamically from a PostgreSQL table

Original notes (outdated since pg 9.4)

We would need a json_array_elements_text(json), the twin of json_array_elements(json) to return proper text values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text value from a scalar json value. I seem to be missing that one, too.
So I improvised with trim(), but that will fail for non-trivial cases ...

Or in a shorter way than Reza:

SELECT settings::json FROM users;

Then, for selecting language for instance:

SELECT settings::json->'language' FROM users;

More details on the official documentation.

TL;DR

SELECT json_agg(t) FROM t

for a JSON array of objects, and

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )
FROM t

for a JSON object of arrays.

List of objects

This section describes how to generate a JSON array of objects, with each row being converted to a single object. The result looks like this:

[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]

9.3 and up

The json_agg function produces this result out of the box. It automatically figures out how to convert its input into JSON and aggregates it into an array.

SELECT json_agg(t) FROM t

There is no jsonb (introduced in 9.4) version of json_agg. You can either aggregate the rows into an array and then convert them:

SELECT to_jsonb(array_agg(t)) FROM t

or combine json_agg with a cast:

SELECT json_agg(t)::jsonb FROM t

My testing suggests that aggregating them into an array first is a little faster. I suspect that this is because the cast has to parse the entire JSON result.

9.2

9.2 does not have the json_agg or to_json functions, so you need to use the older array_to_json:

SELECT array_to_json(array_agg(t)) FROM t

You can optionally include a row_to_json call in the query:

SELECT array_to_json(array_agg(row_to_json(t))) FROM t

This converts each row to a JSON object, aggregates the JSON objects as an array, and then converts the array to a JSON array.

I wasn't able to discern any significant performance difference between the two.

Object of lists

This section describes how to generate a JSON object, with each key being a column in the table and each value being an array of the values of the column. It's the result that looks like this:

{"a":[1,2,3], "b":["value1","value2","value3"]}

9.5 and up

We can leverage the json_build_object function:

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )
FROM t

You can also aggregate the columns, creating a single row, and then convert that into an object:

SELECT to_json(r)
FROM (
    SELECT
        json_agg(t.a) AS a,
        json_agg(t.b) AS b
    FROM t
) r

Note that aliasing the arrays is absolutely required to ensure that the object has the desired names.

Which one is clearer is a matter of opinion. If using the json_build_object function, I highly recommend putting one key/value pair on a line to improve readability.

You could also use array_agg in place of json_agg, but my testing indicates that json_agg is slightly faster.

There is no jsonb version of the json_build_object function. You can aggregate into a single row and convert:

SELECT to_jsonb(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

Unlike the other queries for this kind of result, array_agg seems to be a little faster when using to_jsonb. I suspect this is due to overhead parsing and validating the JSON result of json_agg.

Or you can use an explicit cast:

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )::jsonb
FROM t

The to_jsonb version allows you to avoid the cast and is faster, according to my testing; again, I suspect this is due to overhead of parsing and validating the result.

9.4 and 9.3

The json_build_object function was new to 9.5, so you have to aggregate and convert to an object in previous versions:

SELECT to_json(r)
FROM (
    SELECT
        json_agg(t.a) AS a,
        json_agg(t.b) AS b
    FROM t
) r

or

SELECT to_jsonb(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

depending on whether you want json or jsonb.

(9.3 does not have jsonb.)

9.2

In 9.2, not even to_json exists. You must use row_to_json:

SELECT row_to_json(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

Documentation

Find the documentation for the JSON functions in JSON functions.

json_agg is on the aggregate functions page.

Design

If performance is important, ensure you benchmark your queries against your own schema and data, rather than trust my testing.

Whether it's a good design or not really depends on your specific application. In terms of maintainability, I don't see any particular problem. It simplifies your app code and means there's less to maintain in that portion of the app. If PG can give you exactly the result you need out of the box, the only reason I can think of to not use it would be performance considerations. Don't reinvent the wheel and all.

Nulls

Aggregate functions typically give back NULL when they operate over zero rows. If this is a possibility, you might want to use COALESCE to avoid them. A couple of examples:

SELECT COALESCE(json_agg(t), '[]'::json) FROM t

Or

SELECT to_jsonb(COALESCE(array_agg(t), ARRAY[]::t[])) FROM t

Credit to Hannes Landeholm for pointing this out