Get the value from nested JSON in Postgres

Question

I have a table called "Audio" with a column "transcript" as the following:

{"transcript": [
    {"p": 0, "s": 0, "e": 320, "c": 0.545, "w": "This"}, 
    {"p": 1, "s": 320, "e": 620, "c": 0.825, "w": "call"}, 
    {"p": 2, "s": 620, "e": 780, "c": 0.909, "w": "is"}, 
    {"p": 3, "s": 780, "e": 1010, "c": 0.853, "w": "being"}
    ...
    ]}

I would like to get the value of "p" where "w" matches certain keywords.

If I do the following query, it will give me the entire 's' entries of Audio where one of its "w" has words "google" or "all."

select json_array_elements(transcript->'transcript')->>'s' 
from Audio, 
   json_array_elements(transcript->'transcript') as temp 
where temp->>'w' ilike any(array['all','google'])

How could I get only value of "p" where the condition is satisfied?

Edit: How could I get the value of "p" and its corresponding Audio ID at the same time?

teppic · Accepted Answer · 2016-12-11 18:50:11Z

1

Select your transcript array elements into a common table expression and match from there:

WITH transcript AS (
    SELECT json_array_elements((transcript -> 'transcript')) AS line
    FROM audio
)
SELECT line ->> 'p'
FROM transcript
WHERE line ->> 'w' ILIKE ANY (ARRAY ['all', 'google']);

This will select matching lines from all rows in the audio table. I'm guessing that you'll want to restrict the results to a subset of rows, in which case you'll have to narrow the query. Assuming an id column, do something like this:

WITH transcript AS (
    SELECT
      id,
      json_array_elements((transcript -> 'transcript')) AS line
    FROM audio
    WHERE id = 1
)
SELECT
  id,
  line ->> 'p'
FROM transcript
WHERE line ->> 'w' ILIKE ANY (ARRAY ['call', 'google'])

edited Dec 11, 2016 at 18:50

answered Dec 11, 2016 at 10:03

teppic

7,3161 gold badge34 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

YAL Over a year ago

thanks for the answer! Follow up question for you: how could I get both of the value of p and its corresponding ID while avoid the cross join condition? I have tried selecting both line ->> 'p' and id but i think the result is double counting everything.

teppic Over a year ago

I've added some comments to the example. I had to make an assumption about where the id was coming from, but hopefully the example is general enough.

Collectives™ on Stack Overflow

Get the value from nested JSON in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related