postgreSQL: jsonb traversal

Question

I currently have a table which contains a column with a JSON object representing Twitter cashtags.

For example, this is my original query:

SELECT
DATA->'id' as tweet_id,
DATA->'text' as tweet_text,
DATA->'entities'->'symbols' as cashtags
FROM documents
LIMIT 10

The cashtags column will return something like

[{"text":"HEMP","indices":[0,5]},{"text":"MSEZ","indices":[63,68]}]

How can I traverse this datatype, which is listed as jsonb, in order to say, only return results where the text is equal to HEMP or MSEZ?

klin · Accepted Answer · 2017-10-07 19:29:17Z

The value data->'entities'->'symbols' is a json array. You can unnest the array using the function jsonb_array_elements(), e.g.:

SELECT
    data->'id' as tweet_id,
    data->'text' as tweet_text,
    value as cashtag
FROM documents,
jsonb_array_elements(data->'entities'->'symbols')
where value->>'text' in ('HEMP', 'MSEZ');

 tweet_id | tweet_text |                cashtag                
----------+------------+---------------------------------------
 1        | "my_tweet" | {"text": "HEMP", "indices": [0, 5]}
 1        | "my_tweet" | {"text": "MSEZ", "indices": [63, 68]}
(2 rows)

or:

SELECT DISTINCT
    data->'id' as tweet_id,
    data->'text' as tweet_text,
    data->'entities'->'symbols' as cashtags
FROM documents,
jsonb_array_elements(data->'entities'->'symbols')
WHERE value->>'text' in ('HEMP', 'MSEZ');

 tweet_id | tweet_text |                                   cashtags                                   
----------+------------+------------------------------------------------------------------------------
 1        | "my_tweet" | [{"text": "HEMP", "indices": [0, 5]}, {"text": "MSEZ", "indices": [63, 68]}]
(1 row)

Collectives™ on Stack Overflow

postgreSQL: jsonb traversal

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related