Question 211036 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Query value in nested json field

The table is called "process_flows" and the column of interest is called "results"

Process_flows Table:

contact_uuid result
2f12c383-e131-4454-8713 see text below

The result column contains a nested json that looks like the sample below:

{
    "assessment_dialog": {
        "name": "Assessment Dialog",
        "value": "200",
        "category": "Success",
        "node_uuid": "dba105ed-57c6-4d1e-b8ca-2e312c7dd87b",
        "input": "POST https://dummy.com",
        "extra": {
            "choices": null,
            "formatType": "string",
            "max": null,
            "max_error": "",
            "min": null,
            "min_error": "",
            "pattern": "",
            "message": "What is your name?nn_Please type your name, or the name you want us to use._nnReply *BACK* to go to the previous question.",
            "explanations": "",
            "step": 3,
            "optionId": null,
            "path": "/assessments/ea0813fc-53d6-4b2f-b6a9-202432404e6d/dialog/next",
            "cardType": "INPUT",
            "description": "What is your name?",
            "pdf_media_id": "",
            "title": ""
        },
        "created_on": "2023-07-16T21:35:50.741325153Z"
    },
    "cardtype": {
        "name": "CardType",
        "value": "INPUT",
        "node_uuid": "80941c51-62c1-46a4-bbd1-7be50053aa1d",
        "created_on": "2023-07-16T21:35:50.741708602Z"
    },
    "cardtype_split": {
        "name": "cardType split",
        "value": "INPUT",
        "category": "Input",
        "node_uuid": "2147ad70-f472-4517-843b-c3456604e2e4",
        "input": "INPUT",
        "created_on": "2023-07-16T21:35:50.742223888Z"
    },
    "check_if_error": {
        "name": "Check if Error",
        "value": "FALSE",
        "category": "Other",
        "node_uuid": "3ead361b-e163-4576-9505-bc38c0964d7b",
        "input": "FALSE",
        "created_on": "2023-07-16T21:35:50.742535285Z"
    },
    "choicecontext": {
        "name": "choiceContext",
        "value": "",
        "node_uuid": "099c23dc-c24a-406b-aa66-32beea3ac026",
        "created_on": "2023-07-16T21:35:50.742409274Z"
    },
    "choices": {
        "name": "choices",
        "value": "",
        "node_uuid": "099c23dc-c24a-406b-aa66-32beea3ac026",
        "created_on": "2023-07-16T21:35:50.74240149Z"
    },
    "contact_reply": {
        "name": "contact reply",
        "value": "1",
        "category": "Other",
        "node_uuid": "f17e88aa-62a2-4097-8a44-1c21a2fe9a51",
        "input": "1",
        "created_on": "2023-07-16T21:35:50.565141173Z"
    },
    "description": {
        "name": "Description",
        "value": "What is your name?",
        "node_uuid": "80941c51-62c1-46a4-bbd1-7be50053aa1d",
        "created_on": "2023-07-16T21:35:50.741878847Z"
    },
    "error": {
        "name": "error",
        "value": "",
        "node_uuid": "61fb733d-793a-4792-9bcf-00a8f3673dd0",
        "created_on": "2023-07-16T21:35:50.565275784Z"
    },
    "explanations": {
        "name": "explanations",
        "value": "",
        "node_uuid": "d7362c4e-feb7-4264-b666-de0ed5551f71",
        "created_on": "2023-07-16T21:35:50.742132444Z"
    },
    "formattype": {
        "name": "formatType",
        "value": "string",
        "node_uuid": "099c23dc-c24a-406b-aa66-32beea3ac026",
        "created_on": "2023-07-16T21:35:50.742261153Z"
    },
    "iserror": {
        "name": "iserror",
        "value": "FALSE",
        "node_uuid": "d868ea8d-f7cd-4a34-8392-06c537c816ab",
        "created_on": "2023-07-16T21:35:50.741672008Z"
    },
    "max": {
        "name": "max",
        "value": "",
        "node_uuid": "099c23dc-c24a-406b-aa66-32beea3ac026",
        "created_on": "2023-07-16T21:35:50.742298674Z"
    },
    "max_error": {
        "name": "max error",
        "value": "",
        "node_uuid": "099c23dc-c24a-406b-aa66-32beea3ac026",
        "created_on": "2023-07-16T21:35:50.742327262Z"
    },
    "message": {
        "name": "message",
        "value": "What is your name?nn_Please type your name, or the name you want us to use._nnReply *BACK* to go to the previous question.",
        "node_uuid": "80941c51-62c1-46a4-bbd1-7be50053aa1d",
        "created_on": "2023-07-16T21:35:50.741907836Z"
    },
    "min": {
        "name": "min",
        "value": "",
        "node_uuid": "099c23dc-c24a-406b-aa66-32beea3ac026",
        "created_on": "2023-07-16T21:35:50.742361422Z"
    },
    "min_error": {
        "name": "min error",
        "value": "",
        "node_uuid": "099c23dc-c24a-406b-aa66-32beea3ac026",
        "created_on": "2023-07-16T21:35:50.742392134Z"
    },
    "msisdn": {
        "name": "msisdn",
        "value": "2349039756628",
        "node_uuid": "87b80fd5-6b02-4044-95a0-949ce2986c2b",
        "created_on": "2023-07-16T21:14:31.229769036Z"
    },
    "optionid": {
        "name": "OptionId",
        "value": "",
        "node_uuid": "80941c51-62c1-46a4-bbd1-7be50053aa1d",
        "created_on": "2023-07-16T21:35:50.741776681Z"
    },
    "path": {
        "name": "path",
        "value": "/assessments/ea0813fc-53d6-4b2f-b6a9-202432404e6d/dialog/next",
        "node_uuid": "80941c51-62c1-46a4-bbd1-7be50053aa1d",
        "created_on": "2023-07-16T21:35:50.741811755Z"
    },
    "pattern": {
        "name": "Pattern",
        "value": "",
        "node_uuid": "099c23dc-c24a-406b-aa66-32beea3ac026",
        "created_on": "2023-07-16T21:35:50.742443817Z"
    },
    "pdf_media_id": {
        "name": "PDF Media ID",
        "value": "",
        "node_uuid": "80941c51-62c1-46a4-bbd1-7be50053aa1d",
        "created_on": "2023-07-16T21:35:50.741936948Z"
    },
    "resources": {
        "name": "resources",
        "value": "",
        "node_uuid": "f0d7ffc8-dd77-4a43-a382-ffce6daf6ca3",
        "created_on": "2023-07-16T21:22:05.441680904Z"
    },
    "roadblock": {
        "name": "Roadblock",
        "value": "INPUT",
        "category": "Other",
        "node_uuid": "c9bb3073-b117-4372-a8ae-6a58f3462f39",
        "input": "INPUT",
        "created_on": "2023-07-16T21:35:50.741655325Z"
    },
    "step": {
        "name": "step",
        "value": "3",
        "node_uuid": "80941c51-62c1-46a4-bbd1-7be50053aa1d",
        "created_on": "2023-07-16T21:35:50.741747967Z"
    },
    "title": {
        "name": "Title",
        "value": "",
        "node_uuid": "80941c51-62c1-46a4-bbd1-7be50053aa1d",
        "created_on": "2023-07-16T21:35:50.741839141Z"
    },
    "value": {
        "name": "value",
        "value": "1",
        "node_uuid": "61fb733d-793a-4792-9bcf-00a8f3673dd0",
        "created_on": "2023-07-16T21:35:50.565266618Z"
    },
    "webhook_failure_count": {
        "name": "Webhook Failure Count",
        "value": "0",
        "node_uuid": "87b80fd5-6b02-4044-95a0-949ce2986c2b",
        "created_on": "2023-07-16T21:14:31.229591231Z"
    }
}

It’s a lot of data.

I’m trying to access the "step" value in results:

"step":3

I want a result like this:

Step
3

My query:

select contact_uuid, results->>'step' as step, 
from process_flows

I only need to see step in results and not every value in the nested json.

My query:

SELECT results -> 'extra' ->> 'step'
FROM process_flows

Error:

Error running query: operator does not exist: text -> unknown LINE 1: ...Queue: queries, Query ID: adhoc */ SELECT results -> 'extra'... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

I’m only trying to access the "step" key-value in the json.
Thanks

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The solution was to cast the field to jsonb:

    SELECT cast(results AS jsonb) -> 'assessment_dialog' -> 'extra' ->> 'step' AS step,

  2. 0

    You have two keys named "step," one at the top level with a value that is a JSON object, and one with an integer value nested within the JSON object with key "assessment_dialog". I’m assuming only the latter is desired. The following query uses an explicit path to extract the desired value as an integer:

    SELECT contract_uuid, (results #>> '{assessment_dialog, extra, step}')::integer AS step
  FROM process_flows;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search