Question: Use key values from JSONB nested object in Postgres query

Question

Use key values from JSONB nested object in Postgres query

Answers 0
Added at 2017-09-06 20:09
Tags
Question

I have a query that is working but I need to use an internal key value from a nested value to dynamically change the JSON path in my select query to read the relevant information.

My query:

select 
    (metrics ->> 'name') as name1,
    (metrics #> '{results,[retrieve from nested object],lift,value}') as value1,
    (metrics #> '{results,[retrieve from nested object],lift,is_significant}') as is_significant1   
    from (select json_array_elements(metrics::json) as metrics
    from cust_results_v2_experiment
) s;

    "results": {
        "8544580983": {
            "lift": {
                "value": -0.0076530085106203405,
                "lift_status": "worse",
                "significance": 0.20330659177423127,
                "is_significant": false,
                "visitors_remaining": 418210,
                "confidence_interval": [
                    -0.120946375811231,
                    0.09382997911081951
                ]
            },
            "name": "Template C",
            "rate": 0.034117079963579884,
            "level": "variation",
            "value": 1274,
            "samples": 37342,
            "is_baseline": false,
            "variation_id": "8544580983"
        },
        "8546020144": {
            "name": "Original",
            "rate": 0.03438019186451578,
            "level": "variation",
            "value": 1283,
            "samples": 37318,
            "is_baseline": true,
            "variation_id": "8546020144"
        },

The key value for the object immediately following the "Results" key varies by row and nested object. However the query needs to retrieve the values for each nested object into an individual row.

I would need to repeat the query once for the value 8544580983 and repeat for the next value 8546020144 and so on as the object can vary. I want to accomplish the following for example:

8544580983 (key) to path "value": -0.0076530085106203405,

8544580983 | -0.0076530085106203405

and repeat with text object if the path existed:

8546020144 (key) to path "value": -0.9564654,

8546020144 | -0.9564654

I think that I would have to iterate through the object key/values to substitute the id in the JSON path but I am not sure how I would go about this or what is the best way to proceed. Any help would be greatly appreciated.

Answers to

Use key values from JSONB nested object in Postgres query

Source Show
◀ Wstecz