json,postgresql , how PostgreSQL update the column type json with json array


how PostgreSQL update the column type json with json array

Question:

Tag: json,postgresql

In PostgreSQL, my column type is json , the data is json array like:

[{"attsId": "42a2ce04-52ab-4a3c-8dfb-98c3d14b307d", "planId": 46, "filePath": "fileOperate\\upload", "cfileName": "潜在客户名单 (1).xls", "ufileName": "42a2ce04-52ab-4a3c-8dfb-98c3d14b307d.xls"}, {"attsId": "1adb2f13-00b0-4780-ae76-7a068dc3289c", "planId": 46, "filePath": "fileOperate\\upload", "cfileName": "潜在客户名单.xls", "ufileName": "1adb2f13-00b0-4780-ae76-7a068dc3289c.xls"}, {"attsid": "452f6c62-28df-47c7-8c30-038339f7b223", "planid": 48.0, "filepath": "fileoperate\\upload", "cfilename": "技术市场印花税.xls", "ufilename": "452f6c62-28df-47c7-8c30-038339f7b223.xls"}]

i want update one of the array date like:

UPDATE plan_base set atts->1='{"planId":"71"}' where id= 46;

how to do it? help me please


Answer:

Here are two helper functions, to achieve your goal (requires PostgreSQL 9.3+):

This one can be used like UPDATEs (only updates an index, if it's already exists):

CREATE OR REPLACE FUNCTION "json_array_update_index"(
  "json"            json,
  "index_to_update" INTEGER,
  "value_to_update" anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('[', string_agg("element"::text, ','), ']')::json
  FROM (SELECT CASE row_number() OVER () - 1
                 WHEN "index_to_update" THEN to_json("value_to_update")
                 ELSE "element"
               END "element"
          FROM json_array_elements("json") AS "element") AS "elements"
$function$;

This one can be used, like an UPSERT (updates an index, if it exists, or creates, if not -- using some default value to fill up unused indexes):

CREATE OR REPLACE FUNCTION "json_array_set_index"(
  "json"            json,
  "index_to_set"    INTEGER,
  "value_to_set"    anyelement,
  "default_to_fill" json        DEFAULT 'null'
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('[', string_agg((CASE "index"
                                 WHEN "index_to_set" THEN to_json("value_to_set")
                                 ELSE COALESCE("json" -> "index", "default_to_fill")
                               END)::text, ','), ']')::json
  FROM generate_series(0, GREATEST("index_to_set", json_array_length("json") - 1)) AS "index"
$function$;

With these, you can UPDATE any json data, like:

UPDATE plan_base
SET    atts = json_array_update_index(atts, 1, '{"planId":"71"}'::json)
WHERE  id = 46;

Important! Json arrays are indexed from 0 (unlike other PostgreSQL arrays). My functions respect this kind of indexing.

SQLFiddle

More about updating a JSON object:

Update: functions are now compacted.


Related:


php array returns undefined but print_r shows otherwise [duplicate]


php,arrays,json,undefined
This question already has an answer here: PHP: “Notice: Undefined variable” and “Notice: Undefined index” 11 answers foreach($jsonmediationinfo as $value1) { echo $value1['mstatus']; print_r($jsonmediationinfo); } Output: 1Array ( [0] => Array ( [mstatus] => 1 [mhearingnum] => first [mminutes] => adakjaflafjlarjkelfkalfkd;la ) [1] => Array ( [mhearingnum] => second...

Create n:m objects using json and sequelize?


javascript,json,node.js,sequelize.js
I am trying to learn sequelize, but am having trouble getting a n:m object created. So far I have my 2 models that create 3 tables (Store, Product, StoreProducts) and the models below: models/Store.js module.exports = (sequelize, DataTypes) => { return Store = sequelize.define('Store', { name: { type: DataTypes.STRING, },...

Parsing Google Custom Search API for Elasticsearch Documents


json,python-2.7,elasticsearch,google-search-api
After retrieving results from the Google Custom Search API and writing it to JSON, I want to parse that JSON to make valid Elasticsearch documents. You can configure a parent - child relationship for nested results. However, this relationship seems to not be inferred by the data structure itself. I've...

Return integer value of age(date) function in Postgres


postgresql
I would like to determine the number of days that an account has been open. Ideally, I would like to compare the return value to an integer(days). i.e I would like to see if age(open_date) > 14 If anyone has any better ideas... Thanks...

Translation of interval


postgresql,datetime,translation,intervals,postgresql-8.4
There is maybe a way to translate automatically interval as it's shown by postgresql (e.g. "1330 days 10:54:54.266684") to other languages? For instance using locales, or other settings or I have to use replace, e.g. regexp_replace((now()-t.another_date)::text, 'day','dzień')?

Use JSON file to insert data in database


javascript,json,mongodb,meteor,data
I'm using my JSON file like this to insert data in my collection : var content = JSON.parse(Assets.getText('test.json')); console.log('inserting...'); Profiles.insert({ user: id, data:content }; But I would like to have a "data's tree" like that : [ user: "rtegert23423131", firstname:"test", surname:"test2", // ... ] Not like that : [ user:...

Postgresql Update JDBC


java,postgresql,jdbc
I want to update table: id integer NOT NULL, "first" character varying(255), "last" character varying(255), age integer, CONSTRAINT registration_pkey PRIMARY KEY (id) using method: void updateTable(String tableName, String columnName, String value, String columnName2, String value2) { try { String sql = "UPDATE " + tableName + " SET " +...

Redirect if ActiveRecord::RecordNotUnique error exists


ruby-on-rails,postgresql,activerecord,error-handling
I have the next code to save: Transaction.create(:status => params[:st], :transaction_id => params[:tx], :purchased_at => Time.now).save! But how can I redirect to main root page if this ActiveRecord::RecordNotUnique error appears? Can I catch this error?...

How to work with django-rest-framework in the templates


json,django,django-templates,django-rest-framework
Please bear with me. I am just learning django-rest-framework. And I really can't seem to grab it. model: class Day(models.Model): date = models.DateField(default=date.today) class ToDo(models.Model): date = models.ForeignKey(Day) name = models.CharField(max_length=100) very_important = models.BooleanField(default=False) finished = models.BooleanField(default=False) normal view: def home(request): days = Day.objects.all() return render(request, 'test.html', { 'days':days })...

String comparison in AngularJS


javascript,json,angularjs,ionic-framework,string-comparison
I'm trying to compare two strings in AngularJS, and I've seen examples online. As I understand it, you can use angular.equals(str1, str2), you can use ===, you can use == if you're sure that both are strings... I've tried all three, but I don't get the result. Something must be...

How use jquery getJSON with a local variable


javascript,jquery,ajax,json,ace-editor
i want to extend the completions of Ace Editor, and i need a json of word, this is my code: function load_completions() { var object = $('#preview').attr('class'); $.ajax({ type: "POST", url: "test_editor_ajax.php", data: {action:'load_completion', object:object}, cache: false, success: function(json){ var adminCompleter = { getCompletions: function(editor, session, pos, prefix, callback) {...

How can I get json objects without the object number?


javascript,jquery,json,rest
I have a simple json object that spits out 4 items that have completely different properties inside each one. I have got the json being displayed with the 4 objects that are called meta.work_content like so: [Object, Object, Object, Object] I can open these in console and see the objects...

Deserializing JSON into c# class


c#,json,couchdb
I have a JSON in the following format (from couchDB view) {"rows":[ {"key":["2015-04-01","524","http://www.sampleurl.com/"],"value":1}, {"key":["2015-04-01","524","http://www.sampleurl2.com/"],"value":2}, {"key":["2015-04-01","524","http://www.sampleurl3.com"],"value":1} ]} I need to create a "service" to get this data from couchDB and insert it on SQL Server (for generating reports..) in a efficient way. My first bet was to bulk insert this json...

access the json encoded object returned by php in jquery


php,jquery,ajax,json
I want to post some data to php function by ajax, then get the encoded json object that the php function will return, then I want to get the information (keys and values) from this object, but I don't know how, here is my code: $.ajax({ url: "functions.php", dataType: "JSON",...

Call function on Server from iOS app - Objective C


ios,objective-c,json,server,backend
If you are familiar with Parse.com's Javascript SDK, this is what I am trying to do for my own server for my iOS app (Objective-c). I want to be able to send some a string to the function that is on my server, have the server run its function and...

Serializing a java bean into a cookie: Is it bad?


java,json,cookies
In the organization that i work for, there was a serious debate about the following. Scenario: There is a POJO with 6 different properties all are of type Strings. These values need to be persisted as cookies so that it can be picked back when someone does a booking on...

KendoUI Grid - Complex JSON with inconsistent keys


javascript,json,kendo-ui,kendo-grid
I know this is probably been asked before, but having tried to find an answer - I am guessing either I am not comprehending some of the answers right, or I am looking at the problem all wrong. I am using a complex SLC loopback query - and the api...

Insert data in collection at Meteor's startup


javascript,json,meteor,data,startup
I would like to insert data at Meteor's startup. (And after from a JSON file) At startup, I create a new account and I would like to insert data and link it to this account once this one created. This is the code that creates the new account at startup:...

Replacing elements in an HTML file with JSON objects


javascript,json,replace
I am writing some Javascript to: Read in and Parse a JSON file Read in an HTML file (created as a template file) Replace elements in the HTML file with elements from the JSON file. It is only replacing the first element obj.verb. Can someone please help me figure out...

Link to another resource in a REST API: by its ID, or by its URL?


json,api,rest,api-design,hateoas
I am creating some APIs using apiary, so the language used is JSON. Let's assume I need to represent this resource: { "id" : 9, "name" : "test", "customer_id" : 12, "user_id" : 1, "store_id" : 3, "notes" : "Lorem ipsum example long text" } Is it correct to refer...

Getting front value from array/json


php,arrays,json
I want to get my image value wich could be images/thispicture.png for example. I have an Array or Json with some values, but the problem is i only want the image from array. This is what i get now from the array: {"image_intro":"images\/16748007940_1cc8f06622_small.jpg","float_intro":"","image_intro_alt":"","image_intro_caption":"","image_fulltext":"","float_fulltext":"","image_fulltext_alt":"","image_fulltext_caption":""}"><img...

Complicated Json processing - PHP


php,json
I am using this JSON: http://steamcommunity.com/id/mitch8910/inventory/json/730/2/ I am sorting through the "rgDescriptions" data with this: $data = file_get_contents('http://steamcommunity.com/id/mitch8910/inventory/json/730/2/'); $json = json_decode($data); foreach ($json->rgDescriptions as $mydata) { //checking and adding data to a database } Each section in "rgDescriptions" contains a lot of information on a certain item. For each item...

How to rearrange CSV / JSON keys columns? (Javascript)


javascript,json,csv,papaparse
I am converting a JSON object array to CSV using Papa Parse JavaScript Library. Is there a way to have the CSV columns arranged in a certain way. For e.g; I get the column as: OrderStatus, canOp, OpDesc, ID, OrderNumber, FinishTime, UOM, StartTime but would like to be arranged as:...

PostgreSQL: trigger to call function with parameters


postgresql
In PostgreSQL, I'm trying to create a trigger that passes an argument to a function, but it appears that this operation looks for a function whose signature has zero arguments: ERROR: function create_rec_if_needed() does not exist: CREATE TRIGGER after_update_winks AFTER UPDATE ON winks FOR EACH ROW WHEN (NEW.counter > 3)...

JQuery mutiple post in the same time


javascript,php,jquery,json
I have three functions ,and every function post to special php page to get data.. and every function need some time because every php script need some time .. function nb1() { $.post("p1.php", { action: 1 }, function(data) { console.log(data); }, "json") .fail(function(data) { console.log("error"); }); } function nb2() {...

Save a hex-string to PostgreSQL column character varying


postgresql,hex
I want to save hex-string ('A2-5A-47-00-10-00-00-00') into a PostgeSQL database. My column is character varying(30). How can I do this?...

postgresql complex group by in query


sql,postgresql
I have a query that gives: itemid deadlineneeded delievrydate quantity 200 15/07/15 14/07/15 5 200 15/07/15 14/07/15 10 200 15/07/15 13/07/15 25 201 15/07/15 14/07/15 30 200 14/07/15 10/07/15 3 201 15/07/15 15/07/15 100 It gives the information from multiple tables. Basically it means When items arrive to warehouse (delievrydate)...

Check for duplicates in JSON


javascript,jquery,json,duplicates
I have a json object: var object1 = [{ "value1": "1", "value2": "2", "value3": "3", }, { "value1": "1", "value2": "5", "value3": "7", }, { "value1": "6", "value2": "9", "value3": "5", }, { "value1": "6", "value2": "9", "value3": "5", }] Now I want to take each record out of that...

Codeigniter Select JSON, Insert JSON


json,codeigniter,select,insert,routing
I have very simple users database: user_id, user_name, user_email My model this: class Users extends CI_Model { private $table; private $table_fields; private $table_fields_join; function __construct() { parent::__construct(); $this->table = 'users'; $this->table_fields = array( $this->table.'.user_id', $this->table.'.user_name', $this->table.'.user_email' ); $this->table_fields_join = array(); } function select(){ $this->db->select(implode(', ', array_merge($this->table_fields, $this->table_fields_join)));...

Error while trying to insert data using plpgsql


postgresql,timestamp,plpgsql
I have the following plpgsql function: CREATE OR REPLACE FUNCTION test_func(OUT pid bigint) RETURNS bigint AS $BODY$ DECLARE current_time timestamp with time zone = now(); BEGIN INSERT INTO "TEST"( created) VALUES (current_time) RETURNING id INTO pid; END $BODY$ LANGUAGE plpgsql; select * from test_func(); The above gives an error: column...

SwiftyJSON Reading JSON Array issue


json,swift,swifty-json
I'm trying to read a json array data as below with my code but I'm getting empty result all the time while I can print the whole data. Please where would be my issue? I've read this topic but I couldn't get the result. Topic Link {"Cars":[{"Brand":"Alfa Romeo"},{"Brand":"Audi"},{"Brand":"BMW"},{"Brand":"Citroen"},{"Brand":"Dacia"},{"Brand":"Fiat"},.......... My code...

Is there a better way to write this query involving a self select?


sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

why i don't get return value javascript


javascript,jquery,html,json,html5
When i debug my code i can see i have value but i don't get value to createCheckBoxPlatform FN function createCheckBoxPlatform(data) { var platform = ""; $.each(data, function (i, item) { platform += '<label><input type="checkbox" name="' + item.PlatformName + ' value="' + item.PlatformSK + '">' + item.PlatformName + '</label>' +...

Sencha/Extjs rest call with all parameters


json,rest,extjs,sencha-touch
I'm using ExtJs 5.1.1 and I've written a simple view with a grid, and selecting one row the corresponding model property are editable in some text fields. When editing is completed the button 'save' call Model.save() method, which use the rest proxy configured to write the changes on the server....

need help specifying potentially reserved words as strings in postgres query


postgresql
I've been using mysql for close to 10 years, but have recently jumped into a project that's exposed me to postgres for the first time. Most of the differences aren't a bit deal, but I have been running into some small issues along the way that are throwing me off....

Deserializing Json data to c# for use in GridView - Error Data source is an invalid type


c#,json,gridview,serialization,.net-4.5
I am relatively new to working with C# (v5.0) and Json data (Newtonsoft.Json v6.0) and am seeking assistance in resolving an error when attempting to populate a .Net 4.5 GridView control. My sample Json data as returned by a web service is: { PersonDetails: [ { Book: "10 ", FirstName:...

Fatal error catched by register_shutdown_function and update json_encode


php,json,fatal-error
I can catch a fatal error with register_shutdown_function('shutdown') function shutdown(){ $error = error_get_last(); $result['message'] = $error['message']; } and I have an echo json_encode($result); Unfortunately this echo json_encode shows nothing because json is not updated with the message of the fatal error. What can I do to fix this?...

Prepared statements: Using unnamed and unnumbered question mark style positional placeholders


postgresql
In SQL systems other than Postgres, such as MySQL for instance, prepared statements can use question marks ? as a placeholder for data in prepared statements. INSERT INTO foo (id, name) VALUES (?, ?), (?, ?); However, in Postgres the only available placeholders seem to be the numbered placeholders, so...

convert from json to array


javascript,php,json
I am trying to get data to display in a table. I don't know what I am doing wrong, but when I get the data from my page it is an array of single characters. I could parse this myself but would prefer to know what I am doing wrong....

do calculation inside JSONArray in Java


java,arrays,json
I have a simple issue but cannot solve it as I am not very good at algorithm! I have an JSONArray in this form: [{"values":[{"time":1434976493,"value":"50"}, {"time":1434976494,"value":"100"}],"counter":"counter1"}, {"values":[{"time":1434976493,"value":"200"}, {"time":1434976494,"value":"300"}],"counter":"counter2"}, {"values":[{"time":1434976493,"value":"400"}, {"time":1434976494,"value":"600"}],"counter":"total"}] What I want to do is to get the integer value for counter 1 and counter 2 and then divide...

Why i get can not resolve method error in class android?


android,json
I use this tutorial: JSON and part of code to this: protected String doInBackground(String... urls) { TextView lbl=(TextView) findViewById(R.id.provCODE); person = new Person(); person.setName(lbl.getText().toString()); //person.setCountry("1853"); //person.setTwitter("1892"); return POST(urls[0],person); } but in the this line: person.setName(lbl.getText().toString()); i get this error: Can not resolve method. How can i solve that?what happen? my...

Sorting in Ruby on rails


ruby-on-rails,json,sorting
I'm very new to Ruby on rails. I try to edit the following api. I want to sorting "can_go" which is true are shown at the top of list. I added this row before sending data, but the result is still order by "user_id". user_infos.sort { |a, b| - (a['can_go']<=>b['can_go'])...

Can't save json data to variable (or cache) with angularjs $http.get


json,angularjs,web-services,rest
I have weird angularjs problem. I'm trying to fetch data from Rest Webservice. It works fine, but I can't save json data to object. My code looks like: services.service('customerService', [ '$http', '$cacheFactory', function($http, $cacheFactory) { var cache = $cacheFactory('dataCache'); var result = cache.get('user'); this.getById = function(id){ $http.get(urlList.getCustomer + id).success(function(data, status,...

'utf8' codec can't decode byte 0xf3


python,json,character-encoding
I am using python 2.7 to read the json file. my code is import json from json import JSONDecoder import os path=os.path.dirname(os.path.abspath(__file__))+'/json' print path for root, dirs, files in os.walk(os.path.dirname(path+'/json')): for f in files: if f.lower().endswith((".json")): fp=open(root + '/'+f) data = fp.read() print data.decode('utf-8') But I got the following error....

Parse JSON output from AlamoFire


json,swift,nsurlrequest
I am using the latest Alamofire to manage a GET http request to my server. I am using the following to GET and parse the JSON: Alamofire.request(.GET, "*******") .responseJSON {(request, response, JSON, error) in if let statusesArray = JSON as? NSArray{ if let aStatus = statusesArray[0] as? NSDictionary{ //OUTPUT SHOWN...

LineChart with C3 using JSON


json,c3
I can't understand because my chart is not working properly. Anything is displayed. Below my simple code: <div id="chartc3"></div> <script> var scene; $.getJSON('assets/json/chartc3.json', function(data) { scene=data; var chart = c3.generate({ bindto: '#chartc3', data: { json: scene, keys: { value: ['round','val'], } } }); }); </script> and the following json file:...

Response 200 OK but Jquery shows error?


javascript,jquery,json
I am stuck in kind of a weird problem where I am trying to make a JSONP request to the Open Weather history API to get the weather information for the previous years on the a particular date. fetchHistoryUrl = 'http://78.46.48.103/data/3.0/days?day=0622&mode=list&lat=39.77476949&lon=-100.1953125'; $.ajax({ method: 'get', url: fetchHistoryUrl, success: function(response){ console.log(response); },...

Nested JSON structure to build side-by-side d3.js charts


javascript,json,d3.js
I'm currently stuck on how to traverse a JSON structure (I created) in order to create side-by-side donut charts. I think I've created a bad structure, but would appreciate any advice. I'm working from Mike Bostock's example here: http://bl.ocks.org/mbostock/1305337 which uses a .csv file for source data. In that example...

How to parse JSON array of string arrays


c#,json
Lets say I get the following json data from a web service which I can't change. [ [ "Header1", "Header2", "Header3", "Header4" ], [ "FirstValue1", "FirstValue2", "FirstValue3", "FirstValue4" ], [ "SecondValue1", "SecondValue2", "SecondValue3", "SecondValue4" ] ] jsonlint.com tells me that it is valid json and from what I know I...

Uncaught error: Invalid type for google table column


javascript,json,google-maps,google-visualization
I recently started using google visualization due to its simplicity. But while implementing in my recent project i seem to have run into a bit of trouble. The program shown is used to pull data from a JSON object and utilize it to display data in the google tables. Now...