Filter by data in nested JSON

Need help
1

I need help filtering by data that’s in a nested JSON.

I want to filter the below News by ticker sentiment if the ticker is DLTR and the relevance score for DLTR is 0.5 or higher.

Screenshot 2024-06-08 at 10.31.49 AM
Screenshot 2024-06-08 at 10.31.49 AM1612×1080 119 KB

I know how to filter by each separately but not in conjunction with each other. I tried a couple different ways (in Advanced below) trying to figure it out but haven’t figured it out. The below, once completed, would filter if any relevance score is 0.5 or higher and not necessarily the one I’m trying to get.

Any help/ideas is appreciated!

Screenshot 2024-06-08 at 10.53.35 AM
Screenshot 2024-06-08 at 10.53.35 AM1408×890 122 KB

2

Use two filter operators. First filter by the ticker is DLTR and second filter operator filter by relevance score.

3

But wouldn’t relevance score still filter by all the relevance scores under ticker sentiment and not just the DLTR one?

4

If you first use a filter operator for DLTR then you will be left with the results that have a ticker of DLTR. Then if you use a second filter operator for relevance score and filter out all relevance scores that are less than 0.5 you will have only results whose ticker is DLTR and relevance score is greater than or equal to 0.5

Why would it filter by ticker sentiment if you are not use a filter for ticker sentiment?

You have 4 values:

  1. Ticker - solved by filtering so that Ticker must be DLTR
  2. Relevance Score - solved by filter so that relevance score must be greater than or equal to 0.5
  3. Ticker Sentiment Score - Not relevant to your filters
  4. Ticker Sentiment Label - Not relevant to your filters
5

Thanks for bouncing this back and forth with me!

Because those 4 values are nested under ticker_sentiment (not to be confused with the ticker_sentiment_score that’s nested as well).

I’m filtering News Feeds where ticker sentiment each items ticker contains DLTR count > 0

But I only want it if that ticker’s relevance score is > 0.5. My goal is to only keep news that’s related to the stock and has a higher relevance score.

1 Like
6

Bumping this up in the feed as I still haven’t figured it out

7

Did you try my suggestion and it did not work?

8

Yeah I’d already tried that and it didn’t work.

9

Your Type of Things should not be News body Feed, it should be Ticker_Sentiment

Your datasource should be Result of Step 1 News body feed list of ticker_sentiments

Then you will be working with the correct data and the filters should work.

Sometimes it is difficult to find our way to the correct data when working with API data, especially the type that provides nested JSON

Think about it like this…in bubble database, you have 2 data types, one called News Body and another called ticker_sentiment.

On the News Body data type you will have a field that is a list and is related to the data type ticker_sentiment.

The ticker_sentiment data type would have 4 fields, ticker, relevance_score, ticker_sentiment_score and ticker_sentiment_label.

So, if this API response is thought of as a Bubble database, you would need to search for News Body feed but your data type is ticker_sentiment, and so the datasource expression would be News Body feed each items ticker_sentiment list field.

If you change the data type and datasource of your RG, the two filters should work to get you the results you want.

10

If I change the type to ticker sentiment I can’t save/access any of the News Body details which is what I ultimately want to save to the database.

Screenshot 2024-06-10 at 8.49.45 AM
Screenshot 2024-06-10 at 8.49.45 AM710×1216 79 KB
Screenshot 2024-06-10 at 8.50.00 AM
Screenshot 2024-06-10 at 8.50.00 AM714×1208 79.5 KB

11

Post the entire raw data response from your api call…this will help with understanding the data you are working with

12

Here’s a sample of the raw data response

{
  "body": {
    "items": "12",
    "sentiment_score_definition": "x <= -0.35: Bearish; -0.35 < x <= -0.15: Somewhat-Bearish; -0.15 < x < 0.15: Neutral; 0.15 <= x < 0.35: Somewhat_Bullish; x >= 0.35: Bullish",
    "relevance_score_definition": "0 < x <= 1, with a higher score indicating higher relevance.",
    "feed": [
      {
        "title": "Activist investor takes $1.9 billion stake in Southwest Airlines, calls for leadership changes | Business",
        "url": "https://edition.cnn.com/2024/06/10/business/southwest-activist-investor-stake/index.html",
        "time_published": "20240610T155700",
        "authors": [
          "Chris Isidore"
        ],
        "summary": "Activist investor takes $1.9 billion stake in Southwest Airlines, calls for leadership changes CNN International ...",
        "banner_image": "https://media.cnn.com/api/v1/images/stellar/prod/ap24083575311673.jpg?c=16x9&q=w_800,c_fill",
        "source": "CNN",
        "category_within_source": "Markets",
        "source_domain": "edition.cnn.com",
        "topics": [
          {
            "topic": "Energy & Transportation",
            "relevance_score": "0.5"
          },
          {
            "topic": "Financial Markets",
            "relevance_score": "0.161647"
          },
          {
            "topic": "Manufacturing",
            "relevance_score": "0.5"
          }
        ],
        "overall_sentiment_score": -0.087155,
        "overall_sentiment_label": "Neutral",
        "ticker_sentiment": [
          {
            "ticker": "JCI",
            "relevance_score": "0.090134",
            "ticker_sentiment_score": "0.040323",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "TXN",
            "relevance_score": "0.090134",
            "ticker_sentiment_score": "0.040323",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "LUV",
            "relevance_score": "0.179121",
            "ticker_sentiment_score": "0.046331",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "BA",
            "relevance_score": "0.179121",
            "ticker_sentiment_score": "-0.088803",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "SFTBF",
            "relevance_score": "0.090134",
            "ticker_sentiment_score": "0.040323",
            "ticker_sentiment_label": "Neutral"
          }
        ]
      },
      {
        "title": "Southwest Airlines Stock Soars on Elliott Investment Stake",
        "url": "https://www.kiplinger.com/investing/stocks/southwest-airlines-stock-soars-on-elliott-investment-stake",
        "time_published": "20240610T153051",
        "authors": [
          "Joey Solitro"
        ],
        "summary": "Southwest Airlines ( LUV ) stock jumped more than 7% out of the gate Monday. The upside comes after The Wall Street Journal on Sunday reported that activist investor Elliott Investment Management has built a stake worth nearly $2 billion in the air carrier.",
        "banner_image": "https://cdn.mos.cms.futurecdn.net/hwgJ7osrMtUWhk5koeVme7-320-80.png",
        "source": "Kiplinger",
        "category_within_source": "n/a",
        "source_domain": "www.kiplinger.com",
        "topics": [
          {
            "topic": "Earnings",
            "relevance_score": "0.360215"
          },
          {
            "topic": "Energy & Transportation",
            "relevance_score": "1.0"
          },
          {
            "topic": "Financial Markets",
            "relevance_score": "0.962106"
          }
        ],
        "overall_sentiment_score": 0.255197,
        "overall_sentiment_label": "Somewhat-Bullish",
        "ticker_sentiment": [
          {
            "ticker": "SPGI",
            "relevance_score": "0.097457",
            "ticker_sentiment_score": "0.127862",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "UAL",
            "relevance_score": "0.193465",
            "ticker_sentiment_score": "0.264431",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "LUV",
            "relevance_score": "0.375722",
            "ticker_sentiment_score": "0.231799",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "DAL",
            "relevance_score": "0.193465",
            "ticker_sentiment_score": "0.264431",
            "ticker_sentiment_label": "Somewhat-Bullish"
          }
        ]
      },
      {
        "title": "ICC Holdings, Diamond Offshore Drilling, CrowdStrike And Other Big Stocks Moving Higher On Monday - ICC Holdings  ( NASDAQ:ICCH ) ",
        "url": "https://www.benzinga.com/news/24/06/39253676/icc-holdings-diamond-offshore-drilling-crowdstrike-and-other-big-stocks-moving-higher-on-monday",
        "time_published": "20240610T152526",
        "authors": [
          "Avi Kapoor"
        ],
        "summary": "U.S. stocks were mixed, with the Dow Jones index falling around 0.2% on Monday. Shares of ICC Holdings, Inc. ICCH rose sharply during Monday's session. Mutual Capital Group announced plans to acquire ICC Holdings, Inc. for $23.50 per share in a $73.8 million transaction.",
        "banner_image": "https://cdn.benzinga.com/files/images/story/2024/06/10/movers.jpeg?width=1200&height=800&fit=crop",
        "source": "Benzinga",
        "category_within_source": "Trading",
        "source_domain": "www.benzinga.com",
        "topics": [
          {
            "topic": "Life Sciences",
            "relevance_score": "0.2"
          },
          {
            "topic": "Financial Markets",
            "relevance_score": "0.108179"
          },
          {
            "topic": "Manufacturing",
            "relevance_score": "0.2"
          },
          {
            "topic": "Energy & Transportation",
            "relevance_score": "0.2"
          },
          {
            "topic": "Technology",
            "relevance_score": "0.2"
          },
          {
            "topic": "Finance",
            "relevance_score": "0.2"
          }
        ],
        "overall_sentiment_score": 0.307956,
        "overall_sentiment_label": "Somewhat-Bullish",
        "ticker_sentiment": [
          {
            "ticker": "KRYS",
            "relevance_score": "0.337811",
            "ticker_sentiment_score": "0.0",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "CRWD",
            "relevance_score": "0.229149",
            "ticker_sentiment_score": "0.207487",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "OSUR",
            "relevance_score": "0.115787",
            "ticker_sentiment_score": "0.0",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "STEP",
            "relevance_score": "0.337811",
            "ticker_sentiment_score": "0.338978",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "DO",
            "relevance_score": "0.115787",
            "ticker_sentiment_score": "0.533142",
            "ticker_sentiment_label": "Bullish"
          },
          {
            "ticker": "LUV",
            "relevance_score": "0.229149",
            "ticker_sentiment_score": "0.0",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "RBA",
            "relevance_score": "0.229149",
            "ticker_sentiment_score": "0.388047",
            "ticker_sentiment_label": "Bullish"
          },
          {
            "ticker": "NISN",
            "relevance_score": "0.229149",
            "ticker_sentiment_score": "0.149499",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "WHD",
            "relevance_score": "0.229149",
            "ticker_sentiment_score": "0.0",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "BCS",
            "relevance_score": "0.115787",
            "ticker_sentiment_score": "0.004679",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "OPRA",
            "relevance_score": "0.229149",
            "ticker_sentiment_score": "0.455164",
            "ticker_sentiment_label": "Bullish"
          },
          {
            "ticker": "TTEC",
            "relevance_score": "0.115787",
            "ticker_sentiment_score": "0.165615",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "GERN",
            "relevance_score": "0.337811",
            "ticker_sentiment_score": "0.009948",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "ICCH",
            "relevance_score": "0.439786",
            "ticker_sentiment_score": "0.338075",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "SMHI",
            "relevance_score": "0.229149",
            "ticker_sentiment_score": "0.445037",
            "ticker_sentiment_label": "Bullish"
          },
          {
            "ticker": "AILE",
            "relevance_score": "0.229149",
            "ticker_sentiment_score": "0.0",
            "ticker_sentiment_label": "Neutral"
          }
        ]
      },
      {
        "title": "Elliott Investment Criticizes Southwest Airlines Execution In Letter, Promises 77% Stock Return Potential - Southwest Airlines  ( NYSE:LUV ) ",
        "url": "https://www.benzinga.com/m-a/24/06/39252593/elliott-investment-criticizes-southwest-airlines-execution-in-letter-promises-77-stock-return-potent",
        "time_published": "20240610T144843",
        "authors": [
          "Anusuya Lahiri"
        ],
        "summary": "Elliott Investment Management, which has a $1.9 billion stake in Southwest Airlines Co LUV, has sent a letter to Southwest's Board of Directors criticizing the company's outdated strategies and poor execution. The reports of Elliott's stake boosted investor confidence, sending the stock price ...",
        "banner_image": "https://cdn.benzinga.com/files/images/story/2024/06/10/Southwest-Airlines.jpeg?width=1200&height=800&fit=crop",
        "source": "Benzinga",
        "category_within_source": "Mergers",
        "source_domain": "www.benzinga.com",
        "topics": [
          {
            "topic": "Energy & Transportation",
            "relevance_score": "0.5"
          },
          {
            "topic": "Financial Markets",
            "relevance_score": "0.266143"
          },
          {
            "topic": "Manufacturing",
            "relevance_score": "0.5"
          }
        ],
        "overall_sentiment_score": -0.009803,
        "overall_sentiment_label": "Neutral",
        "ticker_sentiment": [
          {
            "ticker": "LUV",
            "relevance_score": "0.330286",
            "ticker_sentiment_score": "-0.158246",
            "ticker_sentiment_label": "Somewhat-Bearish"
          },
          {
            "ticker": "BA",
            "relevance_score": "0.223866",
            "ticker_sentiment_score": "0.131779",
            "ticker_sentiment_label": "Neutral"
          }
        ]
      },
      {
        "title": "Dow Jones Firm As Nvidia Falls After Stock Split; Analyst Makes This Elon Musk Pay Call  ( Live Coverage ) ",
        "url": "https://www.investors.com/market-trend/stock-market-today/dow-jones-firm-as-nvidia-falls-after-stock-split-analyst-makes-this-elon-musk-pay-call-live-coverage/",
        "time_published": "20240610T144000",
        "authors": [
          "Investor's Business Daily",
          "MICHAEL LARKIN"
        ],
        "summary": "Dow Jones Firm As Nvidia Falls After Stock Split. Analyst Makes This Elon Musk Pay Call ( Live Coverage ) Investor's Business Daily ...",
        "banner_image": "https://www.investors.com/wp-content/uploads/2024/03/Stock-Nvidia-JensenHuangCEO-04-company.jpg",
        "source": "Investors Business Daily",
        "category_within_source": "n/a",
        "source_domain": "www.investors.com",
        "topics": [
          {
            "topic": "Retail & Wholesale",
            "relevance_score": "0.25"
          },
          {
            "topic": "Financial Markets",
            "relevance_score": "1.0"
          },
          {
            "topic": "Manufacturing",
            "relevance_score": "0.25"
          },
          {
            "topic": "Earnings",
            "relevance_score": "0.310843"
          },
          {
            "topic": "Energy & Transportation",
            "relevance_score": "0.25"
          },
          {
            "topic": "Technology",
            "relevance_score": "0.25"
          }
        ],
        "overall_sentiment_score": 0.091417,
        "overall_sentiment_label": "Neutral",
        "ticker_sentiment": [
          {
            "ticker": "MEDP",
            "relevance_score": "0.152499",
            "ticker_sentiment_score": "0.209296",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "NVDA",
            "relevance_score": "0.152499",
            "ticker_sentiment_score": "-0.157102",
            "ticker_sentiment_label": "Somewhat-Bearish"
          },
          {
            "ticker": "AAPL",
            "relevance_score": "0.202366",
            "ticker_sentiment_score": "-0.271906",
            "ticker_sentiment_label": "Somewhat-Bearish"
          },
          {
            "ticker": "CRWD",
            "relevance_score": "0.152499",
            "ticker_sentiment_score": "0.228559",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "TSLA",
            "relevance_score": "0.25142",
            "ticker_sentiment_score": "-0.104273",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "CEG",
            "relevance_score": "0.102013",
            "ticker_sentiment_score": "0.058831",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "INTC",
            "relevance_score": "0.102013",
            "ticker_sentiment_score": "-0.337148",
            "ticker_sentiment_label": "Somewhat-Bearish"
          },
          {
            "ticker": "V",
            "relevance_score": "0.102013",
            "ticker_sentiment_score": "-0.337148",
            "ticker_sentiment_label": "Somewhat-Bearish"
          },
          {
            "ticker": "HD",
            "relevance_score": "0.102013",
            "ticker_sentiment_score": "0.194235",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "LUV",
            "relevance_score": "0.102013",
            "ticker_sentiment_score": "-0.043606",
            "ticker_sentiment_label": "Neutral"
          },
          {
            "ticker": "BKNG",
            "relevance_score": "0.152499",
            "ticker_sentiment_score": "0.186143",
            "ticker_sentiment_label": "Somewhat-Bullish"
          },
          {
            "ticker": "WMT",
            "relevance_score": "0.102013",
            "ticker_sentiment_score": "0.194235",
            "ticker_sentiment_label": "Somewhat-Bullish"
          }
        ]
      }```
13

This topic was automatically closed after 70 days. New replies are no longer allowed.