I’m working with a JSON string in PostgreSQL, and I’m trying to extract the id property from a nested JSON object. However, I’m struggling to get the desired result, and the query keeps returning null.

Here’s a simplified version of the data I have (changing the format of how the metadata is stored is not an option):

SELECT 
  ('{"tags": ["projects"],
    "doc_id": "None",
    "node": "{"id_": "0de73396-5b02-4e7e-895b-70fd09c1ec0c", "embedding": null}"
   }'::json -> 'node') AS node_json,
   
  ('{"tags": ["projects"],
    "doc_id": "None",
    "node": "{"id_": "0de73396-5b02-4e7e-895b-70fd09c1ec0c", "embedding": null}"
   }'::json -> 'node')::text AS node_text,
   
  (('{"tags": ["projects"],
    "doc_id": "None",
    "node": "{"id_": "0de73396-5b02-4e7e-895b-70fd09c1ec0c", "embedding": null}"
   }'::json -> 'node')::text)::json AS parsed_node,
   
  ((('{"tags": ["projects"],
    "doc_id": "None",
    "node": "{"id_": "0de73396-5b02-4e7e-895b-70fd09c1ec0c", "embedding": null}"
   }'::json -> 'node')::text)::json ->> 'id_') AS node_id; // returns null

Issue:
The final query that attempts to extract the id (node_id) returns null. I suspect the issue might be related to how the nested JSON is being interpreted.

Question:
How can I correctly extract the _id or other properties from the node object in this JSON structure? Any suggestions or insights would be greatly appreciated!