Postgres 12.9 - Getting the value of a json array on matching key-CodePudding

I have a column in my database that stores a json string listing the weapons used in each game activity, what I need to be able to do is return the 'values'->'uniqueWeaponKills'->'basic'->'value' when the 'referenceId' key = 1994645182, and 0 if the key,value pair is not in the column.

Example 'weapons' column data

{
  "weapons": [
    {
      "values": {
        "uniqueWeaponKills": {
          "basic": {
            "value": 14,
            "displayValue": "14"
          }
        },
        "uniqueWeaponPrecisionKills": {
          "basic": {
            "value": 0,
            "displayValue": "0"
          }
        },
        "uniqueWeaponKillsPrecisionKills": {
          "basic": {
            "value": 0,
            "displayValue": "0%"
          }
        }
      },
      "referenceId": 1994645182
    },
    {
      "values": {
        "uniqueWeaponKills": {
          "basic": {
            "value": 2,
            "displayValue": "2"
          }
        },
        "uniqueWeaponPrecisionKills": {
          "basic": {
            "value": 1,
            "displayValue": "1"
          }
        },
        "uniqueWeaponKillsPrecisionKills": {
          "basic": {
            "value": 0.5,
            "displayValue": "50%"
          }
        }
      },
      "referenceId": 1853180924
    }
  ]
}

Edit 1: Using the suggestion from Kendle I got to the following query, I haven't seen a way to dynamically look in each of the array elements instead of having to specify the one to look at. Query

select weapons::json->'weapons'->1->'values'->'uniqueWeaponKills'->'basic'->>'value' as "uniqueWeaponKills",
weapons::json->'weapons'->1->'referenceId' as "weaponId"
from activities
where (weapons::json->'weapons'->1->>'referenceId')::BIGINT = 1687353095;

CodePudding user response：

You could try

SELECT
weapons::json->'values'->> 'uniqueWeaponKills'->>'basic' ->>'value' 
FROM table_name
WHERE 
weapons::json->'referenceId' = 1994645182;

CodePudding user response：

I think I found the solution I am looking for using json_array_elements()

SELECT obj->'values'->'uniqueWeaponKills'->'basic'->>'value' as "uniqueWeaponKills"
FROM   activities a, json_array_elements(a.weapons#>'{weapons}') obj
WHERE  (obj->>'referenceId')::BIGINT = 1687353095;