30396

Postgres 9.3 JSON Output multi-dimensional object

Given this query:-

SELECT id as id, attributes->>'name' as file_name, status from workflow.events where schema='customer' and type='FILE_UPLOAD' id,file_name, status 1,name,status 2,name2,status2

I want to output this structure:-

{ "1" :{"id" :"1", "file_name" : "name", "status" : "status1"}, "2" :{"id" :"2", "file_name" : "name2","status" : "status2"} }

I can do it at the moment using string functions but this seems messy and inefficient. CAn it be done using the native postgresql json functions?

Answer1:

If you want to get two records with json, use row_to_json() function:

with cte as ( select id as id, attributes->>'name' as file_name, status from workflow.events where schema='customer' and type='FILE_UPLOAD' ) select row_to_json(c) from cte as c

output:

{"id":1,"file_name":"name","status":"status"} {"id":2,"file_name":"name2","status":"status2"}

If you want to get json array:

with cte as ( select id as id, attributes->>'name' as file_name, status from workflow.events where schema='customer' and type='FILE_UPLOAD' ) select json_agg(c) from cte as c

output:

[{"id":1,"file_name":"name","status":"status"}, {"id":2,"file_name":"name2","status":"status2"}]

But for you desired output, I can only suggest string transformation:

with cte as ( select id::text as id, file_name, status from workflow.events where schema='customer' and type='FILE_UPLOAD' ) select ('{' || string_agg('"' || id || '":' || row_to_json(c), ',') || '}')::json from cte as c

<strong>sql fiddle demo</kbd></strong>

Recommend

  • uploadify & PHP - upload cancel & page refresh issue
  • how to return response using mongo's aggregate?
  • PHP redirect_to not working
  • Visual Basic, Child Thread Blocking Main Thread
  • SQLite3::NotADatabaseException (file is encrypted or is not a database) in production (Engineyard)
  • How to add cancelImg in Uploadify
  • two forks and the use of wait
  • Alert Dialog was disappearing when user clicks out side
  • Exception when insert records into 3 tables within transaction using slick-plain SQL for play2
  • Avoid losing data type for the partitioned data when writing from Spark
  • Cannot set tcsh exit status using a variable
  • Angular 2 + Jasmine Unit Test - Getting Error TS2345
  • Storing data from SQL in array
  • Cannot convert a char value to money. The char value has incorrect syntax
  • Is there a chance to get -splash: work for SWT applications that require -XstartOnFirstThread?
  • include dlls in visual studio c++ 2008
  • Sensibility of combined Maven/Ant+Ivy build management for dual platform Desktop/Android deployment?
  • Bootstrap (v3.3.4) glyphicons not displayed in IE when refresh page (F5)
  • R Split data.frame using a column that represents and on/off switch
  • Pycharm: Marking a folder as 'sources root' is not recursive for subfolders
  • Problem while Building a Setup Project for a windows Service?
  • How to attach a node.js readable stream to a Sendgrid email?
  • Functions in global context
  • Unity3D & Android: Difference between “UnityMain” and “main” threads?
  • Why value captured by reference in lambda is broken? [duplicate]
  • output of program is not same as passed argument
  • Modifying destination and filename of gulp-svg-sprite
  • Deserializing XML into class C#
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • AT Commands to Send SMS not working in Windows 8.1
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • How do I configure my settings file to work with unit tests?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • apache spark aggregate function using min value
  • python draw pie shapes with colour filled
  • Sorting a 2D array using the second column C++
  • Binding checkboxes to object values in AngularJs
  • How to Embed XSL into XML
  • Unable to use reactive element in my shiny app