Exploring and organizing JSON data with PostgreSQL

I have a database table named api_details where I store a JSON value in the column raw_data. Now, I want to generate a report based on this JSON data with an expected outcome like the following:

action_name          sent_timestamp                     Sent    Delivered
campaign_2475         1600416865.928737 - 1601788183.440805   7504    7483
campaign_d_1084_SUN15_ex 1604220248.153903 - 1604222469.087918 63095   62961

Here is a sample of the JSON OUTPUT:

{
  "header": [
    "#0 action_name",
    "#1 sent_timestamp",
    "#0 Sent",
    "#1 Delivered"
    
  ],
  "name": "campaign - lifetime",
  "rows": [
    [
      "campaign_2475",
      "1600416865.928737 - 1601788183.440805",
      7504,
      7483
    ],
    [
      "campaign_d_1084_SUN15_ex",
      "1604220248.153903 - 1604222469.087918",
      63095,
      62961
    ],
    [
      "campaign_SUN15",
      "1604222469.148829 - 1604411016.029794",
      63303,
      63211 
    ]
  ],
  "success": true
}

I have attempted a query below but it doesn't provide the desired results. I can achieve this using Python by iterating through all elements in the row list.

Is there a simpler solution available in PostgreSQL (version 11)?

SELECT raw_data->'rows'->0 
  FROM api_details
jsonpostgresqljsonb

Answer №1

To extract values from a JSONB array, you can utilize the JSONB_ARRAY_ELEMENTS() function like so:

SELECT (j.value)->>0 AS action_name,
       (j.value)->>1 AS sent_timestamp,
       (j.value)->>2 AS Sent,
       (j.value)->>3 AS Delivered
  FROM api_details
 CROSS JOIN JSONB_ARRAY_ELEMENTS(raw_data->'rows') AS j

Check out the demo here

Keep in mind that if the data type of raw_data is not JSONB, you should adjust the argument within the function to raw_data::JSONB->'rows' for explicit type casting.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

What could be the reason Angular is not refreshing with a JSON file update?

Having trouble using a basic Angular JS app to load data from a JSON file onto a website. The JSON file contains: {"a": "a"} The Angular application setup is as follows: var app = angular.module("app", []) .controller("ctrl", ["ser", function(ser) { ...

angularjsjsonobject

json remove unnecessary detailed timestamps

I need to develop a service that only returns JSON with date, time, and minutes, but the current implementation is displaying everything including milliseconds and seconds in the timestamp. How can I remove the milliseconds and seconds from the output? Be ...

javajsonspringspring-bootjackson

Setting parameters to Labels in Objective-C it is important to have unique

As someone new to iOS Programming, I have encountered an issue with assigning values to labels. Below is the data I receive from a service: ( { EmpName = Peter; Relation = SouthAfrica; }, { EmpName = Smith; Relation = WestIndies; }, { ...

iosobjective-ciphonejsonxcode

Exception being raised despite handling in JQuery AJAX post request

I am facing an issue with my login JSON service that handles username and password. When I make a call using JQuery 1.10.1 AJAX and input the correct credentials, it functions correctly. However, if I input incorrect credentials, the error function handler ...

jqueryajaxjsongoogle-chrome

Unable to parse a java.lang.String object from a START_ARRAY token;

Can someone please assist me with this issue: Encountered an error while trying to read the HTTP message: org.springframework.http.converter.HttpMessageNotReadableException: JSON parse error: Unable to deserialize an instance of java.lang.String from a ST ...

javajsonspringpostgresql

A comparison of parent and child components

To implement a child-parent component relationship in Angular, first create two JSON files: parent.json and child.json. The parent.json file should contain the following data: "Id":10001, "name":"John" "Id":10002, ...

javascriptjsonangular

What is the best way to convert a Java 8 LocalDateTime property to a JavaScript-style Date String when using JAX-RS?

I implemented a RESTful web service using JAX-RS method annotations: @GET @Path("/test") @Produces(MediaType.APPLICATION_JSON) public MyThing test() { MyThing myObject = new MyThing(LocalDateTime.now()); return myObject; } Everything is working s ...

javajsonjax-rsjava-8java-time

Breaking down a large JSON array into smaller chunks and spreading them across multiple files using PHP pagination to manage the

Dealing with a large file containing over 45,000 arrays can be challenging, especially on a live server with high traffic. To address this issue, I used the array_chunk($array, 1000) function to divide the arrays into 46 files. Now, my goal is to access a ...

phparraysjsonmathpagination

Express is utilizing HTML entities for formatting JSON data

We are encountering a problem with our NodeJS application which is based on express and body-parser. The issue arises when certain characters in the REST requests, sent in JSON format, get HTML/XML escaped unexpectedly. We suspect that something within eit ...

node.jsjsonexpress

"Embarking on a journey with Jackson and the power

There have been numerous questions regarding the use of Jackson for serializing/deserializing Java objects using the builder pattern. Despite this, I am unable to understand why the following code is not functioning correctly. The Jackson version being use ...

javajsonjackson

Error Occurred while Uploading Images using Ajax HTML Editor with JSON Data

I am facing an issue with my AJAX HtmlEditorExtender, specifically when trying to upload an image. The error message I receive is as follows: JavaScript runtime error: Sys.ArgumentException: Cannot de-serialize. The data does not correspond to valid JSON. ...

c#javascriptajaxjson

Tips for optimizing JSON parsing and database writing for faster performance

I have a task that involves parsing a 200MB json file and then writing the data into an sqlite3 database using Python. Currently, my code executes successfully but it takes about 9 minutes to complete the entire process. @transaction.atomic def create_dat ...

pythonjsondjangosqlite

Check domains using Jquery, AJAX, and PHP

I'm currently developing a tool to check domain availability. Here is the PHP code I have so far: <?php $domain = $_GET["domname"]; function get_data($url) { $ch = curl_init(); $timeout = 5; curl_setopt($ch, CURLOPT_URL, $url); ...

javascriptphpjqueryajaxjson

Leveraging pandas for extracting data from a specific section within a JSON file

Currently, I am in the process of analyzing my electric bill usage by utilizing hourly data that I downloaded in JSON format. Even though I was excited about it at first (woot!), the process has turned out to be more cumbersome than I anticipated: import ...

pythonjsonpandas

A guide on utilizing jq to extract specific fields from a JSON string

Is there a way to extract specific fields from a JSON file using jq? On running 'jq '.node' out.json', the output contains the word 'null' This is the content of the file named out.json head out.json { "items": [ {"node":" ...

jsonbashjq

Struggling to retrieve Json data through Ajax in Rails 5

Hey there! I'm currently exploring the world of Rails action controllers with Ajax, and I've run into a bit of a snag. I can't seem to retrieve Json data and display it in my console.log using my Ajax function. The GET method works perfectly ...

javascriptjqueryruby-on-railsjsonajax

Uploading JSON file Size into Snowflake Variant Column

Our Snowflake table contains JSON files loaded into a Variant column. The table consists of two columns - File name and Variant column (JSON records). While I can determine the total size of the table using information schema, I am now looking to calculat ...

jsonsizesnowflake-cloud-data-platformvariant

Unconventional issues involving ajax and fundamental data submission

Upon submitting the ajax request, I need to send three data parameters to process.conversation.php: method, s_state, and c_id. The values for s_state and c_id are retrieved from two input fields. After testing with alert(s_state) and alert(c_id), both vari ...

jqueryajaxjson

Finding the smallest value within a collection of JSON objects in an array

Looking at the following list, I am in search of the lowest CPU value: [{'Device': 'A', 'CPU': 10.7, 'RAM': 32.5}, {'Device': 'B', 'CPU': 4.2, 'RAM': 32.4}, {'Device' ...

pythonjsonpython-3.x

Processing Large CSV Files in Node.js

I have this large 70mb .csv file that I need to parse and convert into a json format. Initially, when I tested the conversion on a smaller 500kb test csv using regex, it was easy and worked out perfectly. However, when I attempted to apply the same proce ...

jsonnode.jsregexreadfilefs