CODEWITHSUNDEEP
mysqljsonjsonpath
Colin
Colin

Reputation: 2199

Extract first element of calculated JSONpath response

If I have the following JSON data:

{
  "providers": {
    "abc": {
      "provider": "foo",
      "token": "abc123"
    },
    "def": {
      "provider": "bar",
      "token": "def567"
    }
  }
}

I'd like to find a JSON path that returns the token of the "first" provider, i.e. abc123. The problem comes in finding the "first", since providers is an object, not an array.

I thought I could start with $.providers.* which returns the following array:

[
  {
    "provider": "foo",
    "token": "abc123"
  },
  {
    "provider": "bar",
    "token": "def567"
  }
]

But I can't seem to do anything with that array. e.g. $.providers.*[0] doesn't work.

Is what I'm asking for possible? (If it matters, I'm using MySQL for this, although I'm looking for a general JSONPath solution.)

Upvotes: 1

Views: 2345

Answers (3)

Raymond Nijland
Raymond Nijland

Reputation: 11602

You need to use delivered tables/subqueries to parse out the part you need.

Yes, except I don't know that abc is the first provider.

This parses out the first provider element..

Query

SELECT 
 *
 , records_json_array.json_array->'$[0].token'
FROM (

SELECT 
 records.json->'$.providers.*' AS json_array
FROM (

SELECT 
 '{
  "providers": {
    "abc": {
      "provider": "foo",
      "token": "abc123"
    },
    "def": {
      "provider": "bar",
      "token": "def567"
    }
  }
}' AS json
FROM 
 DUAL
) AS records

) AS records_json_array

Result

| json_array                                                                       | records_json_array.json_array->'$[0].token' |
| -------------------------------------------------------------------------------- | ---------------------------------------------- |
| [{"token": "abc123", "provider": "foo"}, {"token": "def567", "provider": "bar"}] | "abc123"                                       |

demo

Upvotes: 3

Colin
Colin

Reputation: 2199

I ended up solving it like this (using MySQL 5.7, which is why I don't have the ->> syntax):

SET @j = '{
  "providers": {
    "abc": {
      "provider": "foo",
      "token": "abc123"
    },
    "def": {
      "provider": "bar",
      "token": "def567"
    }
  }
}';



SELECT JSON_UNQUOTE(
  JSON_EXTRACT(
    JSON_EXTRACT(@j, "$.providers.*"),
    "$[0].token"
  )
) AS token;

Upvotes: 0

Dave Stokes
Dave Stokes

Reputation: 823

select doc->>"$.providers.abc.provider" from x1;
+----------------------------------+
| doc->>"$.providers.abc.provider" |
+----------------------------------+
| foo                              |
+----------------------------------+

1 row in set (0.0008 sec)

Your path is providers.abc.provider

Upvotes: 0

Related Questions