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

Question

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;

user18098820 · Accepted Answer · 2022-03-20 21:35:52Z

0

You could try

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

See also How to parse JSON in postgresql

answered Mar 20, 2022 at 21:35

user18098820

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

2 Comments

mitchwindoo Over a year ago

Thank you for the suggestion and the reference, I was able to get the value by manually specifying the array number to look in, I wasn't able to find anything in the other post or on Postgres documentation for looking through every array item to find the match, but also even specifying the array number threw an error on the WHERE. Added to OP.

mitchwindoo Over a year ago

I got the where issue fixed by casting to a bigint before performing the check and that works. Just need a way to dynamically check through each array now.

mitchwindoo · Accepted Answer · 2022-03-20 23:28:00Z

0

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;

answered Mar 20, 2022 at 23:28

mitchwindoo

32 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related