Extract elements from JSON array and return them as concatenated string

Hi Alex,

SELECT  string_agg(x->>'letter','') FROM json_array_elements(

'[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8, "row":
12, "value": 10, "letter": "B"}, {"col": 9, "row": 12, "value": 1,
"letter": "C"}, {"col": 10, "row": 12, "value": 2, "letter": "D"}]'::json

) x;

Regards,

Ivan Panchenko
Postgres Professional
the Russian PostgreSQL Company

14.03.2018 19:27, Alexander Farber пишет:

Thank you, Ivan! I am trying to apply your suggestion to my table -

On Wed, Mar 14, 2018 at 5:34 PM, Ivan E. Panchenko <
i.panchenko@postgrespro.ru> wrote:

Hi Alex,

SELECT string_agg(x->>'letter','') FROM json_array_elements(

'[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8, "row": 12,
"value": 10, "letter": "B"}, {"col": 9, "row": 12, "value": 1, "letter":
"C"}, {"col": 10, "row": 12, "value": 2, "letter": "D"}]'::json

) x;

# select * from words_moves where gid=656 order by played desc limit 3;
mid | action | gid | uid | played
|
tiles | score
------+--------+-----+------+-------------------------------+------------------------------------------------------------------------------------------------------+-------
1353 | swap | 656 | 7 | 2018-03-14 17:22:18.430082+01 |
"ЙНРР"
| ¤
1352 | play | 656 | 1199 | 2018-03-14 16:55:45.898379+01 | [{"col": 4,
"row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7, "value": 3,
"letter": "У"}] | 19
1351 | play | 656 | 7 | 2018-03-14 16:38:48.132546+01 | [{"col": 9,
"row": 11, "value": 5, "letter": "Ж"}, {"col": 9, "row": 13, "value": 2,
"letter": "М"}] | 16
(3 rows)

by trying the following:

# select string_agg(x->>'letter', ' ') from (select
jsonb_array_elements(tiles) from words_moves where gid=656 and
action='play' order by played desc limit 5) x;
ERROR: 42883: operator does not exist: record ->> unknown
LINE 1: select string_agg(x->>'letter', ' ') from (select jsonb_arra...
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
LOCATION: op_error, parse_oper.c:728

I am probably missing something obvious?

Regards
Alex

On 03/14/2018 10:02 AM, Alexander Farber wrote:

Thank you, Ivan! I am trying to apply your suggestion to my table -

On Wed, Mar 14, 2018 at 5:34 PM, Ivan E. Panchenko
<i.panchenko@postgrespro.ru <mailto:i.panchenko@postgrespro.ru>> wrote:

Hi Alex,

SELECT  string_agg(x->>'letter','') FROM json_array_elements(

'[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8,
"row": 12, "value": 10, "letter": "B"}, {"col": 9, "row": 12,
"value": 1, "letter": "C"}, {"col": 10, "row": 12, "value": 2,
"letter": "D"}]'::json

) x;

# select * from words_moves where gid=656 order by played desc limit 3;
 mid  | action | gid | uid  |            played
|
tiles                                                 | score
------+--------+-----+------+-------------------------------+------------------------------------------------------------------------------------------------------+-------
 1353 | swap   | 656 |    7 | 2018-03-14 17:22:18.430082+01 |
"ЙНРР"
|     ¤
 1352 | play   | 656 | 1199 | 2018-03-14 16:55:45.898379+01 | [{"col":
4, "row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7, "value":
3, "letter": "У"}]   |    19
 1351 | play   | 656 |    7 | 2018-03-14 16:38:48.132546+01 | [{"col":
9, "row": 11, "value": 5, "letter": "Ж"}, {"col": 9, "row": 13, "value":
2, "letter": "М"}] |    16
(3 rows)

by trying the following:

#  select string_agg(x->>'letter', ' ') from (select
jsonb_array_elements(tiles) from words_moves where gid=656 and
action='play' order by played desc limit 5) x;
ERROR:  42883: operator does not exist: record ->> unknown
LINE 1: select string_agg(x->>'letter', ' ') from (select jsonb_arra...
                           ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LOCATION:  op_error, parse_oper.c:728

I am probably missing something obvious?

Do you still have non-arrays in the tile field?:

/messages/by-id/CAADeyWgYKKaArJb6JK_xEtSO=7aeNaYqBu_ef-D5W7s8EFPfpQ@mail.gmail.com

Regards
Alex

--
Adrian Klaver
adrian.klaver@aklaver.com

On 03/14/2018 10:12 AM, Adrian Klaver wrote:

On 03/14/2018 10:02 AM, Alexander Farber wrote:

Thank you, Ivan! I am trying to apply your suggestion to my table -

On Wed, Mar 14, 2018 at 5:34 PM, Ivan E. Panchenko
<i.panchenko@postgrespro.ru <mailto:i.panchenko@postgrespro.ru>> wrote:

    Hi Alex,

    SELECT  string_agg(x->>'letter','') FROM json_array_elements(

    '[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8,
    "row": 12, "value": 10, "letter": "B"}, {"col": 9, "row": 12,
    "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value": 2,
    "letter": "D"}]'::json

    ) x;

# select * from words_moves where gid=656 order by played desc limit 3;
  mid  | action | gid | uid  |            played |
tiles                                                 | score
------+--------+-----+------+-------------------------------+------------------------------------------------------------------------------------------------------+-------

  1353 | swap   | 656 |    7 | 2018-03-14 17:22:18.430082+01 | "ЙНРР"
|     ¤
  1352 | play   | 656 | 1199 | 2018-03-14 16:55:45.898379+01 |
[{"col": 4, "row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7,
"value": 3, "letter": "У"}]   |    19
  1351 | play   | 656 |    7 | 2018-03-14 16:38:48.132546+01 |
[{"col": 9, "row": 11, "value": 5, "letter": "Ж"}, {"col": 9, "row":
13, "value": 2, "letter": "М"}] |    16
(3 rows)

by trying the following:

#  select string_agg(x->>'letter', ' ') from (select
jsonb_array_elements(tiles) from words_moves where gid=656 and
action='play' order by played desc limit 5) x;
ERROR:  42883: operator does not exist: record ->> unknown
LINE 1: select string_agg(x->>'letter', ' ') from (select jsonb_arra...
                            ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LOCATION:  op_error, parse_oper.c:728

I am probably missing something obvious?

Do you still have non-arrays in the tile field?:

/messages/by-id/CAADeyWgYKKaArJb6JK_xEtSO=7aeNaYqBu_ef-D5W7s8EFPfpQ@mail.gmail.com

I should have looked closer before answering, yes there are:

1353 | swap | 656 | 7 | 2018-03-14 17:22:18.430082+01 | "ЙНРР"

Regards
Alex

--
Adrian Klaver
adrian.klaver@aklaver.com

14.03.2018 20:02, Alexander Farber пишет:

Thank you, Ivan! I am trying to apply your suggestion to my table -

On Wed, Mar 14, 2018 at 5:34 PM, Ivan E. Panchenko
<i.panchenko@postgrespro.ru <mailto:i.panchenko@postgrespro.ru>> wrote:

Hi Alex,

SELECT  string_agg(x->>'letter','') FROM json_array_elements(

'[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8,
"row": 12, "value": 10, "letter": "B"}, {"col": 9, "row": 12,
"value": 1, "letter": "C"}, {"col": 10, "row": 12, "value": 2,
"letter": "D"}]'::json

) x;

# select * from words_moves where gid=656 order by played desc limit 3;
 mid  | action | gid | uid  | played |
tiles                                                 | score
------+--------+-----+------+-------------------------------+------------------------------------------------------------------------------------------------------+-------
 1353 | swap   | 656 |    7 | 2018-03-14 17:22:18.430082+01 | "ЙНРР"
|     ¤
 1352 | play   | 656 | 1199 | 2018-03-14 16:55:45.898379+01 | [{"col":
4, "row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7, "value":
3, "letter": "У"}]   |    19
 1351 | play   | 656 |    7 | 2018-03-14 16:38:48.132546+01 | [{"col":
9, "row": 11, "value": 5, "letter": "Ж"}, {"col": 9, "row": 13,
"value": 2, "letter": "М"}] |    16
(3 rows)

by trying the following:

#  select string_agg(x->>'letter', ' ') from (select
jsonb_array_elements(tiles) from words_moves where gid=656 and
action='play' order by played desc limit 5) x;
ERROR:  42883: operator does not exist: record ->> unknown
LINE 1: select string_agg(x->>'letter', ' ') from (select jsonb_arra...
                           ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LOCATION:  op_error, parse_oper.c:728

I am probably missing something obvious?

Yes, here x is the alias for the record, not for the json field. So you
need to write the query like

select string_agg(x->>'letter', ' ')
from (
   select jsonb_array_elements(tiles) x
   from words_moves
   where gid=656 and action='play'
   order by played desc limit 5
) y;

Regards
Alex

Regards,
Ivan

Thank you -

On Wed, Mar 14, 2018 at 8:41 PM, Ivan E. Panchenko <
i.panchenko@postgrespro.ru> wrote:

Yes, here x is the alias for the record, not for the json field. So you
need to write the query like

select string_agg(x->>'letter', ' ')
from (
select jsonb_array_elements(tiles) x
from words_moves
where gid=656 and action='play'
order by played desc limit 5
) y;

This has worked perfectly:

words=> select string_agg(x->>'letter', ' ')
words-> from (
words(> select jsonb_array_elements(tiles) x
words(> from words_moves
words(> where gid=656 and action='play'
words(> order by played desc limit 5
words(> ) y;
string_agg
----------------
А Н Т Щ П
(1 row)