Create array from list of json dictionaries based on key

Question

Say you have a list of json dictionaries in a BigQuery column called "metadata", like so:

  "mentions": [
    {
      "start": 3,
      "end": 17,
      "username": "xyz",
      "id": "12344566"
    },
    {
      "start": 19,
      "end": 34,
      "username": "abc",
      "id": "988649348"
    },
    {
      "start": 35,
      "end": 47,
      "username": "jfk",
      "id": "293489234"
    }
  ]

How can I extract start's values into an array, recursively?:

[3, 19, 35]

Not simply by accessing elements like [0] [1] etc, but recursively

I've gone ahead and removed the tag spam here; based on the content I've left BigQuery, but if this isn't correct, please edit your question to correctly tag. Why does tagging multiple RDBMS products make my question unclear? — Thom A
– Thom A ♦, Commented May 21 at 13:14

DataScienceAmateur · Accepted Answer · 2025-05-21 21:00:42Z

0

Sorry for this stupid question, I found the solution here BigQuery: Extract values of selected keys from an array of json objects

select ARRAY(
  SELECT JSON_EXTRACT_SCALAR(json_array, '$.start') from UNNEST(JSON_EXTRACT_ARRAY(metadata,"$.mentions"))json_array
) as extracted_start

answered May 21 at 21:00

DataScienceAmateur

1922 gold badges4 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Create array from list of json dictionaries based on key

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related