CODEWITHSUNDEEP
postgresql
apgsov
apgsov

Reputation: 904

postgreSQL: jsonb traversal

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?

Upvotes: 1

Views: 170

Answers (1)

klin
klin

Reputation: 121644

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)

Upvotes: 1

Related Questions