Обсуждение: Extract elements from JSON array and return them as concatenated string

Поиск
Список
Период
Сортировка

Extract elements from JSON array and return them as concatenated string

От
Alexander Farber
Дата:
Good afternoon,

A PostgreSQL 10.3 table contains JSON data like:

[{"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"}]

Please suggest, how to extract only the "letter" values and concatenate them to a string like "ABCD"?

I suppose at the end I should use the ARRAY_TO_STRING function, but which JSON function to use for extracting the "letter" values to an array?

I keep looking at https://www.postgresql.org/docs/10/static/functions-json.html but haven't found a good one yet

Thank you
Alex

Re: Extract elements from JSON array and return them as concatenatedstring

От
"Ivan E. Panchenko"
Дата:
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 пишет:
> Good afternoon,
>
> A PostgreSQL 10.3 table contains JSON data like:
>
> [{"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"}]
>
> Please suggest, how to extract only the "letter" values and 
> concatenate them to a string like "ABCD"?
>
> I suppose at the end I should use the ARRAY_TO_STRING function, but 
> which JSON function to use for extracting the "letter" values to an array?
>
> I keep looking at 
> https://www.postgresql.org/docs/10/static/functions-json.html but 
> haven't found a good one yet
>
> Thank you
> Alex
>

Re: Extract elements from JSON array and return them as concatenated string

От
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


Re: Extract elements from JSON array and return them as concatenatedstring

От
Adrian Klaver
Дата:
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?:

https://www.postgresql.org/message-id/CAADeyWgYKKaArJb6JK_xEtSO%3D7aeNaYqBu_ef-D5W7s8EFPfpQ%40mail.gmail.com

> 
> Regards
> Alex
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Extract elements from JSON array and return them as concatenatedstring

От
Adrian Klaver
Дата:
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?:
> 
> https://www.postgresql.org/message-id/CAADeyWgYKKaArJb6JK_xEtSO%3D7aeNaYqBu_ef-D5W7s8EFPfpQ%40mail.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

Re: Extract elements from JSON array and return them as concatenatedstring

От
"Ivan E. Panchenko"
Дата:
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> 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

			
		

		
	

	
	
							

	
	

	

		

			
Re: Extract elements from JSON array and return them as concatenated string

									
		    

		        
От
 
		    	
Alexander Farber

		    

		    

		        
Дата:

		        




			

		

	

	
							
						
	

	

		

			
				
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)